G
Guest
I have sets of data (variable number of rows) that are separated by one blank
row. I am programmatically going through the data & hiding any rows that do
not meet certain criteria.
The problem is, after hiding my rows, I am left with several areas that are
separated by more than one blank row. Can anyone help to hide these so that
only one blank row is showing?
I tried to adapt the following to work only on the visible cells, but I
understand that offset will not step past hidden cells.
Thanks!
Sub leaveOneBlankRow()
Dim c As Range
Dim rng As Range
Set rng = Nothing
For Each c In Range("a1:a300")
If IsEmpty(c) Then
If IsEmpty(c.Offset(1, 0)) Then
If rng Is Nothing Then
Set rng = c.Offset(1, 0)
Else
Set rng = union(rng, c.Offset(1, 0))
End If
End If
End If
Next c
If Not rng Is Nothing Then
rng.EntireRow.Hidden = True
End If
End Sub
row. I am programmatically going through the data & hiding any rows that do
not meet certain criteria.
The problem is, after hiding my rows, I am left with several areas that are
separated by more than one blank row. Can anyone help to hide these so that
only one blank row is showing?
I tried to adapt the following to work only on the visible cells, but I
understand that offset will not step past hidden cells.
Thanks!
Sub leaveOneBlankRow()
Dim c As Range
Dim rng As Range
Set rng = Nothing
For Each c In Range("a1:a300")
If IsEmpty(c) Then
If IsEmpty(c.Offset(1, 0)) Then
If rng Is Nothing Then
Set rng = c.Offset(1, 0)
Else
Set rng = union(rng, c.Offset(1, 0))
End If
End If
End If
Next c
If Not rng Is Nothing Then
rng.EntireRow.Hidden = True
End If
End Sub