vba fill down formula on multiple columns starting from middle of sheet until end-CodePudding

I am building a macro to append new data to a master sheet. The code to select and copy the new data, then move to first free row of master sheet and paste new data works perfectly

Sheets("master").Select
lastrow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row   1

Sheets("new data").Select
Range("a2").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("master").Select
Range("A" & lastrow).Select
ActiveSheet.Paste

I have a few columns I want to fill with a formula (for the demo, I put 0, actual formula differs) starting from its first empty cell all the way until the end of the sheet. The code to select the first cell of the range I want to fill and input the formula works fine

Range("C" & lastrow).Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "0"

It looks like this:

Now I want to fill from that cell to column H until the last cell with that formula but I cant figure out how to select that dynamically. Static code below (recorded from macro):

Selection.AutoFill Destination:=Range("C31971:H40000" ), Type:=xlFillDefault

My current non working attempt (wont compile because syntax very wrong):

Selection.AutoFill Destination:=Range("C" & lastrow: "H" ), Type:=xlFillDefault
Range(Selection, Selection.End(xlDown)).Select

Desired result:

CodePudding user response：

It seems one can not fill down and right at the same time, so two lines of code are needed.

I tried to fill to the end of the sheet. Excel crashed then, it is to much better to fill only some (hundred)thousand rows.

Range("C" & lastrow).AutoFill Destination:=Range("C" & lastrow & ":" & "H" & lastrow), Type:=xlFillDefault
Range("C" & lastrow & ":" & "H" & lastrow).AutoFill Destination:=Range("C" & lastrow & ":" & "H40000"), Type:=xlFillDefault