VBA Compare next visible row/cell with For Next loop

[szita2000]

Hi Guys.

I have a bit of a brain freeze here.
I wrote a macro that loops through a range and hides any rows that are not matching a criteria. (The corresponding column for the cw selected in A1 cells are not colored)
This is what I left with:

The Cells in A:A with Underlined text mark Category titles (Which are left in by my previous macro)
Anything that is not underlined is a task. (That is left there because in the cw there are coloring on the right.)

What I am trying to achieve here is:
Loop through the visible cells in A:A, and find the cells that are: underlined, and the next visible cell in the loop is underlined too.
This means that the current cell it is just an empty category title with no tasks to display. so I could hide it.


First I was trying to offset from rCell to the next visible cell, but that didn't gave me the correct address. so the code below
Maybe I need some sort of booleans that I can flip on and off?

            'CLEANUP>>>>>>>>>>>>>>>>>
            'Hide the underlined rows without an underlined row underneath
            
            'Setting the new lastrow
            Lastrow = Sheet2.Range("A" & Rows.Count).End(xlUp).Row
            
            'Setting the Range that we will loop through
            Set CleanUpVisibleRng = Range("A5:A" & Lastrow)
            
            'Starting Loop here (Only for visible cells)
            For Each rCell In CleanUpVisibleRng.Columns(1).SpecialCells(xlCellTypeVisible).Cells
             
                    'If cell is underlined
                    If rCell.Font.Underline = xlUnderlineStyleSingle Then
                        
                        'Test for the offset cell
                        If rCell.Offset(1, 0).Font.Underline = xlUnderlineStyleSingle Then
                            Debug.Print "Row " & rCell.row & " should be hidden"
                            
                        End If

                            Else
                            Debug.Print "Row " & rCell.row & " should NOT be hidden"
                            
                        End If

                Next rCell

Anyone could put me out of my misery I would be very happy. :D
Have a great weekend guys!

 

[Fluff]

How about

Sub szita()
   Dim Ar As Areas
   Dim i As Long
   
   Set Ar = Range("A5", Range("A" & Rows.Count).End(xlUp)).SpecialCells(xlVisible).Areas
   For i = 1 To Ar.Count - 1
      If Ar(i).Count = 1 And Ar(i).Font.Underline = xlUnderlineStyleSingle Then
         If Ar(i + 1).Resize(1).Font.Underline = xlUnderlineStyleSingle Then
            Debug.Print "Row " & Ar(i).Row & " should be hidden"
         Else
            Debug.Print "Row " & Ar(i).Row & " should be visible"
         End If
      Else
         Debug.Print "Row " & Ar(i).Row & " should be visible"
      End If
   Next i
End Sub

 

[szita2000]

How about

Sub szita()
   Dim Ar As Areas
   Dim i As Long
  
   Set Ar = Range("A5", Range("A" & Rows.Count).End(xlUp)).SpecialCells(xlVisible).Areas
   For i = 1 To Ar.Count - 1
      If Ar(i).Count = 1 And Ar(i).Font.Underline = xlUnderlineStyleSingle Then
         If Ar(i + 1).Resize(1).Font.Underline = xlUnderlineStyleSingle Then
            Debug.Print "Row " & Ar(i).Row & " should be hidden"
         Else
            Debug.Print "Row " & Ar(i).Row & " should be visible"
         End If
      Else
         Debug.Print "Row " & Ar(i).Row & " should be visible"
      End If
   Next i
End Sub

Thanks Fluff! This is working!

Can I just ask how is this "Areas" working? - Never heard of them...
Is it like an expanding range that you always resize to the cell+1?

I really struggled with this one.

Thanks! I will go now and read up on Areas.

 

[Fluff]

Areas are contiguous cells within the range.
So looking at your image A6,8,18,29 etc are individual areas within the range of column A visible cells, whereas A105:A106 is one area as is A144:A146
HTH