CODEWITHSUNDEEP
vba
artenry
artenry

Reputation: 11

Excel VBA: Finding the last row of a column full of duplicates, but NOT the end of the entire column

I have a large datasheet, where each row has a column that gives it a unique identifier, like so: 

      Row     Identifier      Value
       1           A             3
       2           A             4  
       3           A             7 
       4           B             1  
       5           B             3  
       6           B             0  
       7           C             9

I want to be able to find the row number of the last duplicated identifier in a range. So, for example, if the identifier "B" was some unknown number of rows long, and I wanted to find the number of the last row that had the column identifier "B," how would I do so?

So far I've come up with code to select the row of the FIRST identifier:

' Select first row where first instance of <B> exists:
With ActiveWorkbook.ActiveSheet
    Set First_B_Row = .Range("A:A").Find(What:="B", LookIn:=xlValues)
End With

Dim First_B_RowNumber As Long
First_B_RowNumber = First_B_Row.Row

ActiveWorkbook.ActiveSheet.Rows(First_B_RowNumber).Select

But, I want to be able to find the row number of the LAST identifier in a column full of them. Any ideas?

Upvotes: 1

Views: 1789

Answers (1)

L42
L42

Reputation: 19727

You are absolutely close. Instead of searching top to bottom, then search in reverse like this:

With ActiveWorkbook.ActiveSheet
    Dim Last_B_RowNumber As Long
    Last_B_RowNumber = .Range("A:A").Find(What:="B", After:=[A1], _
        SearchDirection:=xlPrevious).Row
End With

Above code will find the last occurrence of B in Column A and assigns it's row number to a variable.
Your just missing the SearchDirection argument.

Upvotes: 1

Related Questions