auto hiding blank rows and columns

[Jase]

I have a range of data with about 200 rows. Usually a chunk of about 20 rows
towards the bottom is blank. I would like to hide those rows automatically
and if they do suddenly have values to automatically unhide them. Is their a
way to do this?

thanks,

Jase

 

[Mike H]

Hi,

How will they get these values if they are hidden?

Mike

 

[Jase]

Let me try to be more clear.

In my data series I have all my cells linked up to another page for
formating purposes, usually about rows 170 to 195 are blank data. Instead of
showing all these blank rows I would like them to be hidden. Then if data
does appear in these rows they will unhide. I just want a macro that I can
run that will go through and look for blank rows and hide them if they r
blank.

thanks,

Jase

 

[Roger Govier]

Hi Jase

You could use some worksheet activate code to achieve this
Private Sub Worksheet_Activate()
Dim lr As Long
Const lastline = 200
Range("A1:A & lastline).Hidden = False
lr = Cells(Rows.Count, "A").End(xlUp).Row + 1
Range("A" & lr & ":A" & lastline).EntireRow.Hidden = True
End Sub

The Constant lastline determines the end of your range.

Copy the Code above
Alt+F11 to invoke the VB Editor
Paste code into white pane that appears
Alt+F11 to return to Excel

 

[Paul]

Hi Roger,

This is eactly what I'm look for. I have pasted the code into VB but am
getting a Syntax Error with the "Range("A1:A & lastline).Hidden = False" line
highlighted. What would the problem be?

With regards,
pAUL.

 

[Roger Govier]

Hi Paul

My bad.
I missed typing a closing double quote.
The line should read
Range("A1:A" & lastline).Hidden = False