Loop to Ignore Hidden Rows

[JessP]

Hello,

I have a worksheet that's split into groups of data with a heading at the top of each. For instance, row 3 has a heading, then 4-9 have data that goes with it, then row 10 has another heading, and so on. Some of the rows of data are hidden using VBA based on certain values, leaving the occasional header with no data under it.

I'd like to hide the data-less headers, but because there are hidden rows in between, I'm struggling to catch the right rows. Basically, every header row is blank in col M and very row with data is not. Here's the code I've been playing with, but it doesn't continue until it's found the next visible row. Any thoughts on how to make it work?

Dim N as long
Dim I as long
Dim x as long

N = Sheets("FoE").Range(Cells(Rows.Count, "B")).End(xlUp)
For i = 4 To N
x = 1
If Cells(i, 13).Value = "" Then
    If Cells(i, 13).Offset(x, 0).Hidden = True Then
        x = x + 1
    ElseIf Cells(i, 13).Offset(x, 0).Value = "" Then
        Range(i, 13).EntireRow.Hidden = True
    End If
End If
Next i

 

[Rick Rothstein]

I think this snippet of code will do what you intended the snippet of code you posted to do...

[table="width: 500"]
[tr]
	[td]  Dim Cell As Range, LastVisibleCell As Variant
  LastVisibleCell = "M3"
  For Each Cell In Range("M4:M" & Cells(Rows.Count, "M").End(xlUp).Row + 1).SpecialCells(xlVisible)
    If Cell.Value = "" And Range(LastVisibleCell) = "" Then Range(LastVisibleCell).EntireRow.Hidden = True
    LastVisibleCell = Cell.Address
  Next[/td]
[/tr]
[/table]

 

[JessP]

Thanks, Rick, that does exactly what I was looking for - and much more elegantly than my attempt.

 

[Rick Rothstein]

Thanks, Rick, that does exactly what I was looking for - and much more elegantly than my attempt.

You are welcome, but there is a minor flaw in my code. Because the SpecialCells method only looks at the UsedRange, my previous code cannot hide the last header if its data is all hidden. This following code snippet fixes the problem...

[table="width: 500"]
[tr]
	[td]  Dim BottomRow As Long, LastVisibleCell As Variant, Ar As Variant, Cell As Range
  Set Ar = Rows.SpecialCells(xlVisible).Areas
  BottomRow = Ar(Ar.Count).Row
  Cells(BottomRow, "A") = ""
  LastVisibleCell = "M3"
  For Each Cell In Range("M4:M" & BottomRow).SpecialCells(xlVisible)
    If Cell.Value = "" And Range(LastVisibleCell) = "" Then Range(LastVisibleCell).EntireRow.Hidden = True
    LastVisibleCell = Cell.Address
  Next
  Cells(BottomRow, "A").Clear
[/td]
[/tr]
[/table]

 

[JessP]

Oh, yes, that makes sense. Thanks for following up - I appreciate it.