• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2302
  • Last Modified:

Excel 2007 VBA: Finding last row containing specific data

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.
4 Solutions
Anthony BerenguelCommented:
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


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

Rob HensonIT & Database AssistantCommented:
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Rob HensonIT & Database AssistantCommented:
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
Anthony BerenguelCommented:
That's a cool trick, Rob.

I vote for Rob's solution unless you absolutely want a VBA approach.
Elton PascuaCommented:
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.
Rob HensonIT & Database AssistantCommented:
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
ouestqueAuthor Commented:
Awesome tips guys! Thanks so much!

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now