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" wrote:

GettingThere wrote:
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" wrote:

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



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