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:

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,214,285
Messages
6,118,673
Members
448,845
Latest member
MrEbzz

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
Back
Top