Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
hiding rows with a formula
I have a form that I have created in excel and I want to know how to hide
rows automatically, that do not have data in them, with a formula. The form pulls information from other sheets. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
hiding rows with a formula
Select the range of cells you want to check, and run this macro:
Sub HideRows() Dim rn As Range Dim rng As Range Set rng = Selection For Each rn In rng.Cells If (rn.Value = "") Then Rows(rn.Row).Hidden = True End If Next End Sub -- Regards, Dave "Sonia" wrote: I have a form that I have created in excel and I want to know how to hide rows automatically, that do not have data in them, with a formula. The form pulls information from other sheets. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
hiding rows with a formula
I have never used macros, how do I do it?
"David Billigmeier" wrote: Select the range of cells you want to check, and run this macro: Sub HideRows() Dim rn As Range Dim rng As Range Set rng = Selection For Each rn In rng.Cells If (rn.Value = "") Then Rows(rn.Row).Hidden = True End If Next End Sub -- Regards, Dave "Sonia" wrote: I have a form that I have created in excel and I want to know how to hide rows automatically, that do not have data in them, with a formula. The form pulls information from other sheets. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
hiding rows with a formula
First, right click on the tab of the worksheet that has the rows you want
hidden and choose "View Code" (you can also hit Alt+F11 and double click your sheet name). This will open up the VBA window. Second, just paste in the code. Third, from your worksheet, hit Alt+F8, click on the "HideRows" macro, and hit run Does that help? -- Regards, Dave "Sonia" wrote: I have never used macros, how do I do it? "David Billigmeier" wrote: Select the range of cells you want to check, and run this macro: Sub HideRows() Dim rn As Range Dim rng As Range Set rng = Selection For Each rn In rng.Cells If (rn.Value = "") Then Rows(rn.Row).Hidden = True End If Next End Sub -- Regards, Dave "Sonia" wrote: I have a form that I have created in excel and I want to know how to hide rows automatically, that do not have data in them, with a formula. The form pulls information from other sheets. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
hiding rows with a formula
Yes, I figure it out, but it is really slow, the whole screen goes crazy.
I was kind of hoping there was a way it would do it with out me having to press Alt+f11. I want to be able to go in and if the rows pull data they will open up and close on there own if there isn't data. Any such thing? "David Billigmeier" wrote: First, right click on the tab of the worksheet that has the rows you want hidden and choose "View Code" (you can also hit Alt+F11 and double click your sheet name). This will open up the VBA window. Second, just paste in the code. Third, from your worksheet, hit Alt+F8, click on the "HideRows" macro, and hit run Does that help? -- Regards, Dave "Sonia" wrote: I have never used macros, how do I do it? "David Billigmeier" wrote: Select the range of cells you want to check, and run this macro: Sub HideRows() Dim rn As Range Dim rng As Range Set rng = Selection For Each rn In rng.Cells If (rn.Value = "") Then Rows(rn.Row).Hidden = True End If Next End Sub -- Regards, Dave "Sonia" wrote: I have a form that I have created in excel and I want to know how to hide rows automatically, that do not have data in them, with a formula. The form pulls information from other sheets. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
hiding rows with a formula
Might want to try the auto filter then... Data-Filter-Auto Filter. With
each re-pull of the data choose "NonBlanks" from the header drop down list. -- Regards, Dave "Sonia" wrote: Yes, I figure it out, but it is really slow, the whole screen goes crazy. I was kind of hoping there was a way it would do it with out me having to press Alt+f11. I want to be able to go in and if the rows pull data they will open up and close on there own if there isn't data. Any such thing? "David Billigmeier" wrote: First, right click on the tab of the worksheet that has the rows you want hidden and choose "View Code" (you can also hit Alt+F11 and double click your sheet name). This will open up the VBA window. Second, just paste in the code. Third, from your worksheet, hit Alt+F8, click on the "HideRows" macro, and hit run Does that help? -- Regards, Dave "Sonia" wrote: I have never used macros, how do I do it? "David Billigmeier" wrote: Select the range of cells you want to check, and run this macro: Sub HideRows() Dim rn As Range Dim rng As Range Set rng = Selection For Each rn In rng.Cells If (rn.Value = "") Then Rows(rn.Row).Hidden = True End If Next End Sub -- Regards, Dave "Sonia" wrote: I have a form that I have created in excel and I want to know how to hide rows automatically, that do not have data in them, with a formula. The form pulls information from other sheets. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Match then lookup | Excel Worksheet Functions | |||
formula to use when number of rows changes dynamically | Excel Worksheet Functions | |||
Hiding rows from a formula | Excel Worksheet Functions | |||
HIDING ROWS IN PROTECTED WORKSHEETS | Excel Discussion (Misc queries) | |||
Insert rows, keep a formula? | Excel Worksheet Functions |