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
"Husker87" wrote:
> 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" wrote:
>
> > 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" wrote:
> >
> > > 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
> > >
|
Similar Threads
