Finding the Lastrow based on a specific word

[ljexcel]

good afternoon,

I am trying to define a lastrow in column C, but I want it to be defined by the first instance of "Other" in that row. I was wondering is there a formula that will application.match "other". Below is how I have my lastrow defined now but I need help changing it to look for the word other.


With ActiveSheet
LastRow3 = .Cells(.Rows.Count, "C").End(xlUp).Row
End With

 

[Rick Rothstein]

I am trying to define a lastrow in column C, but I want it to be defined by the first instance of "Other" in that row.

Clarification please...

1) When you say "in that row", do you mean the word "Other" can appear in any column, not just Column C?

2) Also, you say "last row" and your code is for a "last row", but you say you want to find the "first instance" of the word "Other"... does that really mean what it says... you could have "Other" on rows 10, 15 and 20 and you want the last row set at 10?

 

[ljexcel]

1) I am sorry, I meant that the word "other" can only appear in Column C.

2) In column C I will have a list like below. So I would want the "last row" code to find the first instance of "Other" the rest of the "others" will be directly below that one. I will be using this last row as a way to determine where to put a sumif formula. In column D I will be placing a sum if formula and I want it to drag it down all the way to Personal row. So I would be using the lastrow-1 to determine where that formula stops.

Fire
Car
Earthquake
Home
Home
Business
Business
Business
Person
Person
Person
Other
Other
Other
Total
Total
Total

 

[ljexcel]

sorry I replied under your post and not to it.

 

[Rick Rothstein]

Give this code line a try...

LastRow3 = Columns("C").Find("Other", Cells(Rows.Count, "C"), xlValues, xlWhole, , xlNext, False).Row

Change the variable name as needed.

 

[ljexcel]

Worked Perfectly!

Thank you!!