Hide rows that are empty without autofilter between data

[Tony]

Hey,

I need a VB script that will hide rows in an invoice that are not full of
data.

If I only have 1 item, but my invoice has 90 lines of possible data, I'd
like for it to hide the 89 remaining rows before the subtotal so it can be
neat and fit nicely on 1 page. If you can help, thanks in advance.

[Ron de Bruin]

Hi Tony

This example will loop through row 1:30 in "Sheet1"
If every cell in column A:G is empty it will hide that row.
After the loop it print the sheet and then unhide the rows.

You can also use this with non contiguous ranges Range("B1,D1:G1")
If the cells in column B and D:G are empty it will hide that row.


Sub Hide_Print_Unhide()
Dim rw As Long
Application.ScreenUpdating = False

With Sheets("Sheet1")
For rw = 1 To 30
If Application.WorksheetFunction.CountA( _
.Cells(rw, 1).Range("A1:G1")) = 0 Then _
.Rows(rw).Hidden = True
Next rw
.PrintOut ' for testing use .PrintPreview
.Range("A1:A30").EntireRow.Hidden = False
End With

Application.ScreenUpdating = True
End Sub


--
Regards Ron De Bruin
http://www.rondebruin.nl



"Tony" <Tony@discussions.microsoft.com> wrote in message news:89E3DC84-335B-47B7-A430-EF9CF71343F6@microsoft.com...
> Hey,
>
> I need a VB script that will hide rows in an invoice that are not full of
> data.
>
> If I only have 1 item, but my invoice has 90 lines of possible data, I'd
> like for it to hide the 89 remaining rows before the subtotal so it can be
> neat and fit nicely on 1 page. If you can help, thanks in advance.

[Tony]

Thank you very much this was helpful and worked

"Ron de Bruin" wrote:

> Hi Tony
>
> This example will loop through row 1:30 in "Sheet1"
> If every cell in column A:G is empty it will hide that row.
> After the loop it print the sheet and then unhide the rows.
>
> You can also use this with non contiguous ranges Range("B1,D1:G1")
> If the cells in column B and D:G are empty it will hide that row.
>
>
> Sub Hide_Print_Unhide()
> Dim rw As Long
> Application.ScreenUpdating = False
>
> With Sheets("Sheet1")
> For rw = 1 To 30
> If Application.WorksheetFunction.CountA( _
> .Cells(rw, 1).Range("A1:G1")) = 0 Then _
> .Rows(rw).Hidden = True
> Next rw
> .PrintOut ' for testing use .PrintPreview
> .Range("A1:A30").EntireRow.Hidden = False
> End With
>
> Application.ScreenUpdating = True
> End Sub
>
>
> --
> Regards Ron De Bruin
> http://www.rondebruin.nl
>
>
>
> "Tony" <Tony@discussions.microsoft.com> wrote in message news:89E3DC84-335B-47B7-A430-EF9CF71343F6@microsoft.com...
> > Hey,
> >
> > I need a VB script that will hide rows in an invoice that are not full of
> > data.
> >
> > If I only have 1 item, but my invoice has 90 lines of possible data, I'd
> > like for it to hide the 89 remaining rows before the subtotal so it can be
> > neat and fit nicely on 1 page. If you can help, thanks in advance.
>
>
>