hiding rows with a formula

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

[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


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

[Sonia]

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.

[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?
--
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.

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

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

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

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