Solved: Insert comment to the 2nd to last active cell

[Shazam]

Hi everyone,

May I have a vba code to place a insert comment to the 2nd to last active cell in column A? I run this report daily so my data fluctuates.

[matthewspatrick]

Sub MakeThatComment()

Dim cel As Range

Set cel = Cells(Rows.Count, 1).End(xlUp).Offset(-1, 0)
On Error Resume Next
cel.Comment.Delete
On Error GoTo 0
With cel
.AddComment
.Comment.Visible = False
.Comment.Text Text:="Your comment here"
End With

End Sub

[lucas]

Sub a()
Range("a65536").End(xlUp).Offset(-2, 0).AddComment ("Hello World")
End Sub

[matthewspatrick]

Steve,

A row offset of -2 will get the third to last cell (or, more accurately, the cell two rows above the last cell).

:)

[lucas]

Oops I misread his question. I also forgot error handling in case there is already a comment in that cell......On error resume next.

I was looking at this and got busy with something else and didn't know you had posted by the time I got to it....

[matthewspatrick]

No worries :hi:

[Shazam]

Thanks matthewspatrick and lucas both of your codes works great.

Nice short code lucas.:)

[lucas]

Glad it works for you. Don't forget to mark your thread solved.