Macro Help Required - Run script until no more data in column. Possible loop needed?

Hi,

I have a simple Button that when pressed will run the following command:

Sub Button2_Click()
'
' Button2_Click Macro
'

'
    Range("B2").Select
    ActiveCell.FormulaR1C1 = "=""'""&RC[-1]&""',"""
    Range("B2").Select
    Selection.AutoFill Destination:=Range("B2:B53"), Type:=xlFillDefault
    Range("B2:B53").Select
    ActiveWindow.SmallScroll Down:=-30
End Sub

The problem I am facing is, the list is column A will vary in length ever day, therfore I do not want to have to edit my code daily. Can someone help me re-code this so it stops when it gets to the last cell that contains data in column A. I know I can simple change Range("B:B") but I am then left with date that needs to be deleted.
Any help would be much appreciated.

Dave

What is your macro trying to achieve?

Please take a moment to read the forum rules and amend your original post to include code tags.

3. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Select your code and click the [#] button at the top of the post window (if you are editing an existing post, press Go Advanced to see the [#] button).

Dom

Sorry i cant reply to you till you put the code tags.

All you need is this code

Dim lrow As Long
lrow = Range("A" & Rows.Count).End(xlUp).Row
Range("B1:B" & lrow).FormulaR1C1 = "=""'""&RC[-1]&""',"""

You can then include the code to copy pastespecial values if required.