How to auto hide an empty row with VBA?

[Applewine]

Is it possible to have Excel hide a row automatically if there is no
entry there?

My worksheet Subtotals data on 5 rows, then creates a Total on the 6th
row.
If there is no data on any row, can I hide them so the Total line
appears right below the last entry?

For example:

Subtotal 1 (SUMIF function used)
Subtotal 2 (SUMIF function used)
Subtotal 3 (SUMIF function used)
Empty Subtotal 4 (SUMIF function used)
Empty Subtotal 5 (SUMIF function used)
TOTAL (SUM of all above)

My first guess is to use Change event, looking at a cell that would
count the number of subtotals that are not empty, then use VBA to hide
the empty ones...?

 

[macropod]

Hi,

Here's a macro that hides all rows that have non-numeric data, or zeros, in
Column B, and unhides them again if their status changes. Modifying it to
work a different range of columns and on a Change event basis (eg
Worksheet_Calculate or WorksheetChange) would be fairly trivial.

Sub CleanUp()
Dim CurrentRow As Long
Dim UsedRows As Range
On Error GoTo Abort
Set UsedRows = ActiveSheet.UsedRange.Rows
For CurrentRow = UsedRows.Rows.Count To 1 Step -1
If
Application.WorksheetFunction.Sum(UsedRows.Rows(CurrentRow).Columns("B:B"))
= 0 Then
UsedRows.Rows(CurrentRow).EntireRow.Hidden = True
Else UsedRows.Rows(CurrentRow).EntireRow.Hidden =
False
End If
Next CurrentRow
' If only hiding for printing purposes, use the next two lines to print or
preview then restore the worksheet
'ActiveWindow.SelectedSheets.PrintPreview
'ActiveSheet.Rows.EntireRow.Hidden = False
Abort:
End Sub



Change the 'Sum" parameter to "Count" if only non-numeric values are to be
used (i.e. zeros are left alone, but empty and text-only rows in the defined
columns are not).

Cheers