Macro range

[1S1]

I receive a sheet of data daily, containing a list of numbers. The number are of several digits long and I convert them all in to six digit numbers in column “B” with the following formula. =text(a1,“000000”) I then click on the bottom right corner of cell A1 and the formula populates down. I then Special pates (values) the result in to cell A1’

I have now recorded a macro to do this process, but when this was set up there were only 500 rows of data, so when I receive a list containing 800 rows of data the macro only works up to row 500.
Can the macro be altered to accommodate as many rows as there are rows of data?

The macro reads:

Range("E3").Select
ActiveCell.FormulaR1C1 = "=TEXT(RC[-3],""000000"")"
Range("E3").Select
Selection.AutoFill Destination:=Range("E3:E500"), Type:=xlFillDefault
Range("E3:E500").Select
Selection.Copy
ActiveWindow.ScrollRow = 1
Range("B3").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Columns("E:E").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("E3").Select
End Sub

 

[Joe4]

Try capturing the last row, something like:

' Capture last row using column A
Dim myLastRow as Long
myLastRow = Range("A65536").End(xlUp).Row
Range("E3").Select
ActiveCell.FormulaR1C1 = "=TEXT(RC[-3],""000000"")"
Range("E3").Select
Selection.AutoFill Destination:=Range("E3:E" & myLastRow), Type:=xlFillDefault
Range("E3:E500").Select
Selection.Copy
ActiveWindow.ScrollRow = 1
Range("B3").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Columns("E:E").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("E3").Select
End Sub

 

[jimboy]

Or this shorter version;

With Range("E3:E" & Range("B65536").End(xlUp).Row)
.Formula = "=TEXT(RC[-3],""000000"")"
.NumberFormat = "@"
.Value = .Value
.Copy .Offset(0, -3)
.Clear
End With