Hi,

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 seconds.

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
Next
Application.ScreenUpdating = True
End Sub


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?

Thanks in advance!