CODEWITHSUNDEEP
excelvbams-office
M. X
M. X

Reputation: 1347

Excel: Find most right (last) occurrence of a value with VBA in a row

In an Excel sheet I want to find, using VBA, the rightmost occurrence of a value in a specific row.

Example: Say I have the following values in row 3 (cells delimited by spaces in this example): 

1 2 1 5 1 5 7 1 4 5 6

Now I want to get the column number of the rightmost occurrence of the value 1. The answer is 8.

How do I get this answer in an efficient way and with a single command? Can i do this with Range.Find()?

Upvotes: 3

Views: 3588

Answers (1)

JosieP
JosieP

Reputation: 3410

Yes you can:

range("3:3").Find(what:=1,searchorder:=xlbycolumns,searchdirection:=xlPrevious).column

if you know there will be at least one occurrence of 1 (otherwise you should test the result range before trying to refer to its column)

Upvotes: 4

Related Questions