Fill the dynamic range of cells in a column with the value

Question

Dears, I'm trying to autofill the range of cells in a column with the value/formula. The main purpose is to have macro that will be applicable for similar reports which will have different number of rows each month.

Have already recorded a macro where I've created a separate column. Put a value in a first cell. And autofilled whole column by double click fill handle. In a macro the below string was created, where "495" is the number of the last filled row in the tested table. Selection.AutoFill Destination:=Range("M2:M495")

However, in this case macro won't work correctly when the number of rows will be greater than 495. In order to make it more dynamic, I've tried to use the following: Selection.AutoFill Destination:=Range(Selection, Selection.End(xlDown)) In this case the macro process with whole column and fill it till the last cell (1048576). Not the best solution.

Could you please advice, what to put in macro instead of "M495" or "Selection.End(xlDown)" in order to have column filled till the last filled row in a table?

Below is the last version of the macro:

Sub Test6()

    Range("E8").Select
    ActiveSheet.ListObjects("report_2023_2_21_143341").Unlist
    Columns("M:M").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("M1").Select
    ActiveCell.FormulaR1C1 = "Count"
    Range("M2").Select
    ActiveCell.FormulaR1C1 = "=RC[-1]-1"
    Range("M2").Select
    Selection.AutoFill Destination:=Range(Selection, Selection.End(xlDown))
    Range(Selection, ActiveCell.End(xlDown)).Select
    Columns("D:D").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("D1").Select
    ActiveCell.FormulaR1C1 = "Data"
    Range("D2").Select
    ActiveCell.FormulaR1C1 = "=RC[-2]+RC[10]"
    Range("D2").Select
    Selection.AutoFill Destination:=Range(Selection, Selection.End(xlDown))
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Font.Bold = True
    Selection.NumberFormat = "dd/mm/yyyy;@"

End Sub

Answer 1

You can try using Cells(Rows.Count,"A").End(xlUp).Row to get the last not empty row and then start pasting after that

LastLine = Cells(Rows.Count,"A").End(xlUp).Row