Find the Last Cell in a Row and Replace the Value

A_K

New Member
Joined
Mar 12, 2013
Messages
8
Hi,


I need to be able to


1) find the last filled cell in a row ( for 2000 rows)
2) replace the value in that cell with a certain text ("YES")

I am not sure where to start and what formula/function to apply.


Could you please help?


Thank you!
A_K
 
Last edited:

Some videos you may like

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

Firefly2012

Well-known Member
Joined
Dec 28, 2011
Messages
3,638
Hi

Will the last cell be a different column for each row? You'll need code for that such as:

Code:
Sub Replace_Last_Cell_in_Row()
Dim r As Range

For Each r In Intersect(Activesheet.UsedRange.EntireRow,Cells(1,Columns.Count).EntireColumn)
  r.End(xlToLeft).Value = "Yes"
Next r
End Sub

Note that the above will replace any header value you might have too.
 

Firefly2012

Well-known Member
Joined
Dec 28, 2011
Messages
3,638

ADVERTISEMENT

Yes, if you don't want that to happen you could amend the code to:

Code:
Sub Replace_Last_Cell_in_Row()
Dim r As Range
Dim i As Long

For Each r In Intersect(Activesheet.UsedRange.EntireRow,Cells(1,Columns.Count).EntireColumn)
  If i > 0 Then r.End(xlToLeft).Value = "Yes"
  i = i + 1
Next r
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,123,241
Messages
5,600,495
Members
414,383
Latest member
konmtu

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