hiding rows with a formula

S

Sonia

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.
 
D

David Billigmeier

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
 
S

Sonia

I have never used macros, how do I do it?

David Billigmeier said:
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
Click to expand...
 
D

David Billigmeier

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?
 
S

Sonia

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?
 
D

David Billigmeier

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.
 
Top