hide empty rows on open

[Joshy]

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!

 

[Dave Peterson]

You turned screenupdating back on at the end of the procedure, but you never
turned it off at the top of the procedure. If you had, it flickering would have
been hidden.

One the chance that you wanted to not do the hiding of rows except for the first
time...

Option Explicit
Dim HasBeenRun As Boolean
Private Sub Worksheet_Activate()
Dim rng As Range
Dim cell As Range

If HasBeenRun = True Then
Exit Sub
End If

Set rng = Nothing
On Error Resume Next
Set rng = Application.Intersect(Me.UsedRange, Me.Range("B6:B120"))
On Error GoTo 0

If rng Is Nothing Then
'no cells in the used range
'do nothing
Else
Application.ScreenUpdating = False
For Each cell In rng.Cells
If Application.CountA(cell.EntireRow) = 0 Then
cell.EntireRow.Hidden = True
End If
Next cell
Application.ScreenUpdating = True
End If

HasBeenRun = True

End Sub


ps. I only looked at the cells in column B. But since you're using the
entirerow, there's no need to loop through columns C, D, ..., G.