Auto Hide Rows with VBA

[andrewb90]

Hello All,

I have the following code that I have been using successfully for a while now:

Private Sub SchedulerPrep()    Rows("88:207").Select
    Selection.EntireRow.Hidden = True
    Rows("164:196").Select
    Selection.EntireRow.Hidden = False
    Rows("164:196").Hidden = False
    For r = 164 To 196
        If r <> 164 And r <> 193 And r <> 194 Then
            If Range("D" & r).Value = "" Then Rows(r).Hidden = True
        End If
    Next r
    Range("A1").Select
End Sub

Now, however I have made a few chances to my sheet, and I can't quite figure out how to get the code to match. For every row that has a value in Column D I need that row and the row immediately below to be visible.

Any help would be greatly appreciated.

 

[Michael M]

you already have

Rows("164:196").Hidden = False

visible

Do you mean if the cell in "D" is NOT empty to hide it ???

Private Sub SchedulerPrep()
    Rows("88:207").Hidden = True
    Rows("164:196").Hidden = False
    For r = 164 To 196
        If r <> 164 And r <> 193 And r <> 194 Then
            If Range("D" & r).Value = "" Then Rows(r & ":" & r + 1).Hidden = True
            End If
    Next r
    Range("A1").Select
End Sub

 

[andrewb90]

No, in the range 164:196 I need all rows that do not have a value in "D" to be hidden, however any row that has a value in D needs the row below it to stay visible.

Heres an example:

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Rows:[/TD]
[TD]Column D[/TD]
[TD]Column E[/TD]
[/TR]
[TR]
[TD]170[/TD]
[TD]Joe[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]171[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]172[/TD]
[TD]Bob[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]173[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]174[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]175[/TD]
[TD]Sam[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]176[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]177[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]178[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]179[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]180[/TD]
[TD]Tom[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]181[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]182[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]183[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]184[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Using this example chart rows 170,171,172,173,175,176,180,& 181 would be visible. Four would be visible because of the values in column D and the other four are because they are in the row directly below.
The remaining rows would stay hidden.

The code is written opposite really, It says that blank cells will be hidden, which was working until I added another condition to make a row remain visible.

 

[Michael M]

ok try..

Private Sub SchedulerPrep()
 Dim r As Long
 Rows("88:207").Hidden = True
    For r = 196 To 164 Step -1
        If r <> 164 And r <> 193 And r <> 194 Then
            If Range("D" & r).Value <> "" Then Rows(r & ":" & r + 1).Hidden = False
            End If
    Next r
    Range("A1").Select
End Sub

 

[Peter_SSs]

No, in the range 164:196 I need all rows that do not have a value in "D" to be hidden, however any row that has a value in D needs the row below it to stay visible.

Assuming the values in column D are constants, not formula results, try

Sub Hide_Show()
  Dim c As Range
  
  Rows("164:196").Hidden = True
  For Each c In Range("D164:D196").SpecialCells(xlConstants)
    c.Resize(2).EntireRow.Hidden = False
  Next c
End Sub