leave one blank row between data in *visible* rows

[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

 

[Ken Johnson]

Hi,

Try this out on a copy of your data.

It seems to work.

Sub leaveOneBlankRow()
Dim c As Range
For Each c In Range("a1:a300")
If Not IsEmpty(c) And c.EntireRow.Hidden = True Then
c.Offset(1, 0).EntireRow.Hidden = True
End If
Next c
End Sub

Ken Johnson

 

[Guest]

Thanks for the reply Ken.

Unfortunately, it doesn't work because there could be many hidden rows
between each blank.

Perhaps I need some sort of loop? Like If IsEmpty(c) then while c.offset
(i,0) ....

I'll keep trying.

Happy New Year!

 

[Ken Johnson]

Thanks for the reply Ken.

Unfortunately, it doesn't work because there could be many hidden rows
between each blank.

Perhaps I need some sort of loop? Like If IsEmpty(c) then while c.offset
(i,0) ....

I'll keep trying.

Happy New Year!

Hi,

If your original data consisted of blocks of multiple rows each
separated from the next block by just one blank row, then the only way
that you could end up with the processed blocks being separated by more
than one blank row is when whole blocks of data have been hidden.
This leads to three types of visible blank rows...

1. those inbetween two hidden rows with data
2. those with hidden data above and visible data below
3. those with visible data above and hidden data below

My next attempt with your problem hides the first and second type of
visible blank row and leaves the third type as the separating blank row
needed...

Public Sub HideAllBarOne()
Dim I As Long
For I = 2 To 299
If (Cells(I - 1, 1).EntireRow.Hidden And _
Cells(I - 1, 1).Value <> "") And _
(Cells(I + 1, 1).EntireRow.Hidden And _
Cells(I + 1, 1).Value <> "") Then
Cells(I, 1).EntireRow.Hidden = True
End If
If (Cells(I - 1, 1).EntireRow.Hidden And _
Cells(I - 1, 1).Value <> "") And _
(Not Cells(I + 1, 1).EntireRow.Hidden And _
Cells(I + 1, 1).Value <> "") Then
Cells(I, 1).EntireRow.Hidden = True
End If
Next I
End Sub

Hope this is a bit more successful.

Happy New Year!

Ken Johnson

 

[Guest]

Hello Ken,

Your comment:

"... then the only way that you could end up with the processed blocks being
separated by more than one blank row is when whole blocks of data have been
hidden..."

....was right on. I hadn't looked at the problem that way, but as soon as I
read that, I realized that all I really needed to do was make sure that I hid
the blank row that followed each block of hidden data. This what I ended up
doing:

Public Sub HideAllButOneTake2()
'work backwards
Dim I As Long

For I = 299 To 2 Step -1
If Cells(I, 1).EntireRow.Hidden = True Then
Cells(I + 1, 1).EntireRow.Hidden = True
End If
Next I
End Sub

Thanks for your help - I appreciate it!

 

[Ken Johnson]

Hello Ken,

Your comment:

"... then the only way that you could end up with the processed blocks being
separated by more than one blank row is when whole blocks of data have been
hidden..."

...was right on. I hadn't looked at the problem that way, but as soon as I
read that, I realized that all I really needed to do was make sure that I hid
the blank row that followed each block of hidden data. This what I ended up
doing:

Public Sub HideAllButOneTake2()
'work backwards
Dim I As Long

For I = 299 To 2 Step -1
If Cells(I, 1).EntireRow.Hidden = True Then
Cells(I + 1, 1).EntireRow.Hidden = True
End If
Next I
End Sub

Thanks for your help - I appreciate it!

Hi,

That's a neat solution.
Thanks for the feedback, it's nice knowing I was of some assistance.

Ken Johnson