Finding the Lastrow based on a specific word

ljexcel

New Member
Joined
Jul 2, 2015
Messages
4
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
 

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,882
Office Version
  1. 2010
Platform
  1. Windows
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

New Member
Joined
Jul 2, 2015
Messages
4
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
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,882
Office Version
  1. 2010
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,735
Messages
5,626,587
Members
416,192
Latest member
steinach

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top