Find the last colored line in a Excel Sheet

user_9329661 0
Viewed 43

I'm using colored cells to make section in Excel sheets with full colored lines.

I'm looking for a way to easily get the address of the las horizontal line fully colored on backgroud.

The way I looking for is with the Find method but I can't make it

With Application.FindFormat
    .Clear
    .Interior.ColorIndex = xlNone
End With

cell = .Find(What:="", After:=.Cells(.Cells.Count), SearchFormat:=True)

Thanks for your help

vba excel
1 Answers

Last Fully Colored Row Address

Option Explicit

Sub GetLastColoredRowAddressTEST()
    Debug.Print GetLastColoredRowAddress(Range("A1:M20"))
    ' Caution! When using for whole worksheet use this:
    Debug.Print GetLastColoredRowAddress(Sheet1.UsedRange.EntireRow)
End Sub

Function GetLastColoredRowAddress( _
    ByVal rg As Range) _
As String
    
    With Application.FindFormat
        .Clear
        .Interior.ColorIndex = xlNone
    End With
    
    Dim rCount As Long: rCount = rg.Rows.Count
    
    Dim rCell As Range
    Dim r As Long
    
    For r = rCount To 1 Step -1
        Set rCell = rg.Rows(r).Find(What:="", _ 
            SearchDirection:=xlPrevious, SearchFormat:=True)
        If rCell Is Nothing Then
            GetLastColoredRowAddress = rg.Rows(r).Address
            Exit For
        End If
    Next r
    
    Application.FindFormat.Clear
    
End Function

Edit

  • Here's a worksheet version. Of course, you should rename the functions as you see fit.
Sub GetLastWorksheetColoredRowAddressTEST()
    Debug.Print GetLastWorksheetColoredRowAddress(Sheet1)
End Sub

Function GetLastWorksheetColoredRowAddress( _
    ByVal ws As Worksheet) _
As String
    
    With Application.FindFormat
        .Clear
        .Interior.ColorIndex = xlNone
    End With
    
    With ws.UsedRange.EntireRow
        Dim rCell As Range
        Dim r As Long
        For r = .Rows.Count To 1 Step -1
            Set rCell = .Rows(r).Find(What:="", _
                SearchDirection:=xlPrevious, SearchFormat:=True)
            If rCell Is Nothing Then
                GetLastWorksheetColoredRowAddress = .Rows(r).Address
                Exit For
            End If
        Next r
    End With
    
    Application.FindFormat.Clear
    
End Function
user_98140690
Related