Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
auto hiding blank rows | Excel Discussion (Misc queries) | |||
Hiding blank rows using a macro | Excel Discussion (Misc queries) | |||
Automatically Hiding Blank Rows | Excel Discussion (Misc queries) | |||
Hiding Rows if the linked rows are blank | Excel Discussion (Misc queries) | |||
Hiding blank rows | Excel Discussion (Misc queries) |