Always evaluate last row of data

[tiredofit]

Assume I have some data on Sheet1 as follows:

Apples
Apples
Oranges
Pineapples
Peaches
Coconuts

Using the code below, the last row evaluates to 6, correctly:

Public Function LRow(ByRef wks As Worksheet) As Long

    On Error GoTo Correction

        With wks
        
            LRow = .Cells.Find(What:="*", _
                               After:=.Cells(Rows.Count, Columns.Count), _
                               SearchDirection:=xlPrevious, _
                               SearchOrder:=xlByRows).Row
            
        End With

    On Error GoTo 0

        Exit Function
Correction:
        LRow = 1

    Resume Next
End Function

However, if the user were to apply a filter to the data first and only want Apples, using the code above evaluates to 2.


What I want is to some function to evaluate to 6, regardless of whether the user has applied a filter to the data.


I don't want to turn off the autofilter by adding:

Sheet1.AutoFilterMode = False

because I assume the user wants to have the autofilter active.


Can it be done?


Thanks

 

[Akuini]

Try:

   LRow = .Cells.Find(What:="*", _
                               After:=.[COLOR=#0000cd]Cells(1, 1)[/COLOR], _
                               SearchDirection:=xlPrevious, _
                               SearchOrder:=xlByRows).Row

 

[tiredofit]

Try:

   LRow = .Cells.Find(What:="*", _
                               After:=.[COLOR=#0000cd]Cells(1, 1)[/COLOR], _
                               SearchDirection:=xlPrevious, _
                               SearchOrder:=xlByRows).Row

Thanks

 

[Akuini]

You're welcome & thanks for the feedback