Solved: Write in Col J just Before close

[justdriving]

Just BeforeClose
First , I want to calculate End Row of Col A
Then, I am looking to write "Thank you" in every cell of COL J, till last row calculated in Col A.



If any row of Col A is blank, then I do not write "Thank you" in Col J of that row.



I want to select Range (A2:J & "End of row") excluding those rows which don't have "Thank you" in respective cell in Col J possibly using filter method, but not using Sort method.


How can I do this in VBA?

[Bob Phillips]

With Worksheets(1)

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
.Range("J1").Value = "Thank you"
End With

[justdriving]

If possible, please help with other conditions.

[Bob Phillips]

Dim rng As Range
Dim LastRow As Long
Dim i As Long

With Worksheets(1)

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = 2 To LastRow

If .Cells(i, "A").Value2 <> "" Then

.Cells(i, "J").Value = "Thank you"
If rng Is Nothing Then

Set rng = .Cells(i, "A").Resize(, 10)
Else

Set rng = Union(rng, .Cells(i, "A").Resize(, 10))
End If
End If
Next i

If Not rng Is Nothing Then rng.Select
End With

[justdriving]

Thanks,

if last column is not column J but I calculated it using .End(xltoleft) then
how can I modify above program to include last col.

This answer would help me a lot.

[Bob Phillips]

Dim rng As Range
Dim LastRow As Long
Dim Lastcol As Long
Dim i As Long

With Worksheets(1)

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
Lastcol = .Cells(1, .Columns.Count).End(xlToLeft).Column
For i = 2 To LastRow

If .Cells(i, "A").Value2 <> "" Then

.Cells(i, "J").Value = "Thank you"
If rng Is Nothing Then

Set rng = .Cells(i, "A").Resize(, Lastcol)
Else

Set rng = Union(rng, .Cells(i, "A").Resize(, Lastcol))
End If
End If
Next i

If Not rng Is Nothing Then rng.Select
End With