Autofill to last cell

[C_Griff]

I'm having trouble in changing my macro to autofill. It is a table and could be 1-5000 lines.

This is what I originally recorded on my macro -

Selection.AutoFill Destination:=Range("F2:F1511")
Range("F2:F1511").Select

I tried changing it to :
Selection.AutoFill Destination:=Range("F2:F" & LastRow")
Range("F2:F" & LastRow").Select

But it didn't work.

Column F will have 00000 in it but I am replacing it with a formula which I want to copy down to last row.

 

[footoo]

Omit the last quote marks :

Selection.AutoFill Destination:=Range("F2:F" & LastRow)
Range("F2:F" & LastRow).Select

 

[C_Griff]

That didn't work. I get runtime 1004 error and it highlights the first line.

 

[Fluff]

Try

Range("F2").AutoFill Destination:=Range("F2:F" & lastrow)
Range("F2:F" & lastrow).Select

 

[C_Griff]

Still getting same error.

 

[MARK858]

Any protection and is F2 a merged cell?

 

[Rick Rothstein]

Do you have a line of code that actually calculates a value for your "lastrow" variable?

 

[C_Griff]

Any protection and is F2 a merged cell?

No protection and it's not a merged cell.

I have downloaded a table and I'm then tidying it up and this column I'm having the issue is changing an existing column that has data I do not require - every cell says 000000 and I am inserting a formula to total the debit & credit columns and then replacing the formula with the value via copy/paste values only so I can then delete the debit and credit column.

I think below is the relevant bit for this action.

Range("F1").Select
ActiveCell.FormulaR1C1 = "Amount"
Columns("F:F").Select
Selection.NumberFormat = "$#,##0.00_);[Red]($#,##0.00)"
Range("F2").Select
ActiveCell.FormulaR1C1 = "=RC[1]-RC[2]"
Range("F2").Select
Selection.AutoFill Destination:=Range("F2:F" & LastRow)
Range("F2:F" & LastRow).Select
Columns("F:F").Select
Selection.Copy
Columns("F:F").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Columns("G:H").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Columns("I:I").Select
Selection.Delete Shift:=xlToLeft
Columns("I:I").Select
Selection.Cut
Columns("H:H").Select
Selection.Insert Shift:=xlToRight
Columns("G:H").Select

 

[footoo]

Try replacing all of the code you posted with this :

LastRow = Cells(Rows.Count, "F").End(xlUp).Row
Range("F1") = "Amount"
Columns("F:F").NumberFormat = "$#,##0.00_);[Red]($#,##0.00)"
With Range("F2:F" & LastRow)
    .FormulaR1C1 = "=RC[1]-RC[2]"
    .Value = .Value
End With
Range("G:H,K:K").Delete Shift:=xlToLeft
Range("I:I").Cut
Range("H:H").Insert Shift:=xlToRight

 

[C_Griff]

Try replacing all of the code you posted with this :

LastRow = Cells(Rows.Count, "F").End(xlUp).Row
Range("F1") = "Amount"
Columns("F:F").NumberFormat = "$#,##0.00_);[Red]($#,##0.00)"
With Range("F2:F" & LastRow)
    .FormulaR1C1 = "=RC[1]-RC[2]"
    .Value = .Value
End With
Range("G:H,K:K").Delete Shift:=xlToLeft
Range("I:I").Cut
Range("H:H").Insert Shift:=xlToRight

That worked perfectly thank you.