Can you hide and unhide rows with a macro based on content of colu

[Husker87]

We have a worksheet that collects rows of information from another sheet. Is
there a way to write a macro that would hide (or unhide as new rows are
added) so when I print it only prints rows with data?

Here is our attempt, which hides the rows but will not unhide the rows when
new rows are added. Suggestions???

Private Sub Worksheet_Change(ByVal Target As Range)

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = False

With ActiveSheet
For Each cell In Range("B6:B30,B38:B62")
If cell.Value = "" Then
cell.EntireRow.Hidden = True
ElseIf cell.Value > 1 Then
cell.EntireRow.Hidden = False
End If
Next
End With

Application.ScreenUpdating = True
End Sub

 

[aushknotes]

The range of cells you're hiding are hard-coded. Once you change the range of
cells "Range("B6:B30,B38:B62")" to include the new rows, your code should
work.

 

[Husker87]

Thanks for the reply… when we input values in the rows within the range (say
row 21 or 22) the macro then hides all the blank rows. The problem comes
when we then enter information that populates into row 23 it does not unhide
row 23. Make sense or should I explain with a hard example? Thanks again
for helping…

 

[aushknotes]

Assuming you always insert new rows at the bottom of the worksheet (I.e.
right after your 2nd range of rows):

Private Sub Worksheet_Change(ByVal Target As Range)

Dim lLastRow As Long

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = False

With ActiveSheet
lLastRow = ws.Cells(Rows.Count, "B").End(xlUp).Row

For Each cell In Range("B6:B30,B38:" & lLastRow)
If cell.Value = "" Then
cell.EntireRow.Hidden = True
ElseIf cell.Value > 1 Then
cell.EntireRow.Hidden = False
End If
Next
End With

Application.ScreenUpdating = True
End Sub

 

[aushknotes]

Too quick when copy & paste. Try the following instead:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim oCell As Range
Dim lLastRow As Long


1 Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = False

With ActiveSheet
lLastRow = .Cells(Rows.Count, "B").End(xlUp).Row

For Each oCell In .Range("B6:B30,B38:B" & lLastRow)
If oCell.Value = "" Then
oCell.EntireRow.Hidden = True
ElseIf oCell.Value > 1 Then
oCell.EntireRow.Hidden = False
End If
Next
End With

Application.ScreenUpdating = True
End Sub