Results 1 to 4 of 4

Thread: Always evaluate last row of data
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Apr 2013
    Posts
    1,055
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    0 Thread(s)

    Default Always evaluate last row of data

    Assume I have some data on Sheet1 as follows:


    Code:
    
    Apples
    Apples
    Oranges
    Pineapples
    Peaches
    Coconuts

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


    Code:
    
    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:


    Code:
    
    Sheet1.AutoFilterMode = False

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


    Can it be done?


    Thanks

  2. #2
    Board Regular Akuini's Avatar
    Join Date
    Feb 2016
    Location
    Indonesia
    Posts
    2,057
    Post Thanks / Like
    Mentioned
    35 Post(s)
    Tagged
    4 Thread(s)

    Default Re: Always evaluate last row of data

    Try:
    Code:
       LRow = .Cells.Find(What:="*", _
                                   After:=.Cells(1, 1), _
                                   SearchDirection:=xlPrevious, _
                                   SearchOrder:=xlByRows).Row

  3. #3
    Board Regular
    Join Date
    Apr 2013
    Posts
    1,055
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Always evaluate last row of data

    Quote Originally Posted by Akuini View Post
    Try:
    Code:
       LRow = .Cells.Find(What:="*", _
                                   After:=.Cells(1, 1), _
                                   SearchDirection:=xlPrevious, _
                                   SearchOrder:=xlByRows).Row
    Thanks

  4. #4
    Board Regular Akuini's Avatar
    Join Date
    Feb 2016
    Location
    Indonesia
    Posts
    2,057
    Post Thanks / Like
    Mentioned
    35 Post(s)
    Tagged
    4 Thread(s)

    Default Re: Always evaluate last row of data

    You're welcome & thanks for the feedback

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •