I have a spreadsheet that has cell references to another spreadsheet.
I am trying to write a macro script that when the document is opened (and refreshes data from the linked spreadsheet) it
looks at the rows which are blank and hides these rows.
So far I have found the following code below which works
great, however this macro re-runs everytime you navigate back to the worksheet, causing the screen to flicker for a few
Private Sub Worksheet_Activate()
Dim rng As Range, cell As Range
Set rng = Application.Intersect(ActiveSheet.UsedRange, Range("B6:G120"))
For Each cell In rng
If Application.CountA(cell.EntireRow) = 0 Then cell.EntireRow.Hidden = True
Application.ScreenUpdating = True
I just want the script to run once.
I have tried replacing the top line of script with:
Private Sub Worksheet_Change(ByVal Target As Range)
However I was then getting a debug error associated with line 3.
Can anybody offer any help?