auto hiding blank rows and columns
 

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

auto hiding blank rows and columns
 

Hi,

How will they get these values if they are hidden?

Mike

"Jase" wrote:

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

auto hiding blank rows and columns
 

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

"Mike H" wrote:

Hi,

How will they get these values if they are hidden?

Mike

"Jase" wrote:

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

auto hiding blank rows and columns
 

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

--
Regards
Roger Govier

"Jase" wrote in message
...
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

"Mike H" wrote:

Hi,

How will they get these values if they are hidden?

Mike

"Jase" wrote:

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

auto hiding blank rows and columns
 

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" wrote:

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

--
Regards
Roger Govier

"Jase" wrote in message
...
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

"Mike H" wrote:

Hi,

How will they get these values if they are hidden?

Mike

"Jase" wrote:

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

auto hiding blank rows and columns
 

Hi Paul

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

--
Regards
Roger Govier

"Paul" wrote in message
...
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" wrote:

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

--
Regards
Roger Govier

"Jase" wrote in message
...
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

"Mike H" wrote:

Hi,

How will they get these values if they are hidden?

Mike

"Jase" wrote:

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