# 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.
Asked:
###### Who is Participating?

Finance AnalystCommented:
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.

Thanks
Rob H
0

Commented:
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
Range(lastOccuranceCellAddress).Select
Else
MsgBox ("'" & findThis & "' does not exist in column A.")
End If

End If
End Sub
``````
0

Commented:
Hi

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
Else
MsgBox "'" & SearchValue & "' could not found on " & SearchRng
End If

End If

End Sub
``````

Kris
0

Finance AnalystCommented:
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&".")

Thanks
Rob H
0

Commented:
That's a cool trick, Rob.

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

Commented:
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
``````

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

Finance AnalystCommented:
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.

Thanks
Rob H
0

Author Commented:
Awesome tips guys! Thanks so much!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

## Already a member? Login.

All Courses

From novice to tech pro — start learning today.