Excel 2007 VBA: Finding last row containing specific data

Posted on 2012-08-20
1 Ratings
Last Modified: 2012-08-22
I have an Excel 2007 spreadsheet.
Sheet1 contains random names..

What VBA would I need so that a user can type a name in cell B1. When he/she presses a button, a messagebox appears stating the very last row in column A that contains the name entered in Cell B1. PLEASE SHOW ME A WAY TO DO THIS WITHOUT USING A LOOP.

Example: Pretend I have the data below in column A. (In real life the data varies.)
Row Column A
1      Billy
2      Billy
3      Billy
4      Sally
5      Sally
6      Timmy
7      Joel
8      Eric
9      Clapton
10    Clapton
11    Clapton
12    Clapton
13    Clapton
14    McDougal

If the user typed Eric in cell B1 and pressed the button a messagebox would appear stating: "The last row Eric appeared on is Row 8"

If the user typed Clapton in cell B1 and pressed the button a messagebox would appear stating: "The last row Clapton appeared on is Row 13"

If the user typed Sally in cell B1 and pressed the button a message box would appear stating: "The last row Sally appeared on is Row 5"

I have tried using "Match" to achieve this goal, but it seems that Match does not work sometimes as I think your data has to be in alphabetical order for that method to always work when finding the last row.
e.g., activeworkbook.activesheet.match(activesheet.range("A1:A14"), Name,1) gave me trouble sometimes. I think this is because all data in column A is not in alphabetical order.
Question by:ouestque
    LVL 10

    Assisted Solution

    by:Anthony Berenguel
    try something like this...

    Public Sub getLastOccuranceOfString()
        Dim findThis As String
        'get value from b1
        findThis = Range("b1").Value
        If Len(findThis) > 0 Then
            Dim lastOccuranceCellAddress As String
            Dim lastOccuranceCellRow As Integer
            For Each c In Range("A:A")
                Debug.Print "c: " & c & ", findThis: " & findThis
                If Trim(LCase(c)) = Trim(LCase(findThis)) Then
                    'get cell row
                    lastOccuranceCellAddress = c.Address
                    lastOccuranceCellRow = c.Row
                End If
                If Len(c) = 0 Then
                    'first occurance of empty cell, assumed to be end of the search list
                    'exit for loop
                    Exit For
                End If
            Next c
            'display results
            If Len(lastOccuranceCellAddress) > 0 Then
                MsgBox ("The last occurance of '" & findThis & "' is located in row: " & lastOccuranceCellRow)
                'select cell
                MsgBox ("'" & findThis & "' does not exist in column A.")
            End If
        End If
    End Sub

    Open in new window

    LVL 18

    Assisted Solution


    Try this

    Sub kTest()
        Dim SearchValue     As String
        Dim SearchCol       As String
        Dim SearchRng       As String
        Dim FoundRow        As Long
        Dim LastRow         As Long
        SearchValue = [b1].Value
        SearchCol = "A"
        If Len(SearchValue) Then
            LastRow = Cells(Rows.Count, SearchCol).End(3).Row
            SearchRng = Cells(1, SearchCol).Resize(LastRow).Address
            On Error Resume Next
            FoundRow = Evaluate("match(2,1/(" & SearchRng & "=""" & SearchValue & """))")
            On Error GoTo 0
            If FoundRow Then
                MsgBox "The last row '" & SearchValue & "' appeared on is Row " & FoundRow
                MsgBox "'" & SearchValue & "' could not found on " & SearchRng
            End If
        End If
    End Sub

    Open in new window

    LVL 31

    Accepted Solution

    A fairly simple formula based suggestion:

    ="The last row "&B1&" appearred on was row "&MATCH(B1,A1:A14,0)+COUNTIF(A1:A14,B1)-1

    Assumes data in A1:A14 and user entry in B1. Also assumes names will be in groups but not ncessarily in alphabetical order.

    Logic: the MATCH finds the first occurence, the COUNTIF counts the number of occurences, therefore first plus number of occurences less 1 equals last.

    Rob H
    LVL 31

    Expert Comment

    by:Rob Henson
    With an error check to allow for user entry of names not in list.

    =IF(ISERROR(MATCH(B1,A1:A14,0)),B1&" is not in the list.","The last row "&B1&" appearred on was row "&MATCH(B1,A1:A14,0)+COUNTIF(A1:A14,B1)-1&".")

    Rob H
    LVL 10

    Expert Comment

    by:Anthony Berenguel
    That's a cool trick, Rob.

    I vote for Rob's solution unless you absolutely want a VBA approach.
    LVL 8

    Assisted Solution

    by:Elton Pascua
    Another method:

    Sub Test()
        Dim ws As Worksheet
        Dim findRange As Range
        Dim findCell As Range
        Dim findWhat As String
        Set ws = ThisWorkbook.Worksheets("Sheet1")
        findWhat = ws.Range("B1").Value
        Set findRange = ws.Range("A1:A" & ws.Cells(Rows.Count, 1).End(xlUp).Row)
        On Error Resume Next
        Set findCell = findRange.Find(What:=findWhat, LookAt:=xlWhole, SearchDirection:=xlPrevious, SearchOrder:=xlByRows)
        On Error GoTo 0
        MsgBox ("The last row " & findWhat & " appeared on is Row " & findCell.Row & ".")
    End Sub

    Open in new window

    Edit: Forgot to change used range on the final code.
    LVL 31

    Expert Comment

    by:Rob Henson
    Looking back at your original question, see below copied from the help screeen for the MATCH function.

    MATCH(lookup_value, lookup_array, [match_type])The MATCH function syntax has the following arguments (argument: A value that provides information to an action, an event, a method, a property, a function, or a procedure.):

    •lookup_value    Required. The value that you want to match in lookup_array. For example, when you look up someone's number in a telephone book, you are using the person's name as the lookup value, but the telephone number is the value you want.
    The lookup_value argument can be a value (number, text, or logical value) or a cell reference to a number, text, or logical value.

    •lookup_array    Required. The range of cells being searched.
    •match_type    Optional. The number -1, 0, or 1. The match_type argument specifies how Excel matches lookup_value with values in lookup_array. The default value for this argument is 1.
    The following table describes how the function finds values based on the setting of the match_type argument.

    Match_type Behavior
    1 or omitted MATCH finds the largest value that is less than or equal to lookup_value. The values in the lookup_array argument must be placed in ascending order, for example: ...-2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE.
    0 MATCH finds the first value that is exactly equal to lookup_value. The values in the lookup_array argument can be in any order.
    -1 MATCH finds the smallest value that is greater than or equal to lookup_value. The values in the lookup_array argument must be placed in descending order, for example: TRUE, FALSE, Z-A, ...2, 1, 0, -1, -2, ..., and so on.
    Your section of code sets the match_type to 1 so the data has to be in ascending order. In my suggestion I have set the match_type to 0 so the data can be in any order but will look for an exact match, hence the need for the error check.

    Rob H

    Author Closing Comment

    Awesome tips guys! Thanks so much!

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Why You Should Analyze Threat Actor TTPs

    After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

    Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
    This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
    This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
    This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

    758 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now