Excel 2007 VBA: Finding last row containing specific data
Posted on 2012-08-20
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
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.