Find the Last Cell in a Row and Replace the Value

[A_K]

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

 

[Firefly2012]

Hi

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

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.

 

[VoG]

Cross posted Find the Last Cell in a Row and Replace the value

 

[A_K]

Thank you Firefly!

Will the last cell be a different column for each row?

Yes, the last cell is different for each column.

Hi

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

Does this mean the column names will change too?

Thank you,
A_K

 

[Firefly2012]

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

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

 

[A_K]

Thank you, Firefly! This worked!

 

[Firefly2012]

You're welcome