Excel VBA Autofill Multiple Unqiue Columns-CodePudding

I have 420 columns, each with a unique formula that changes by row number, with about 1500 rows in total.

For reasons I can't understand, the row number references in each forumla doesn't always update correctly when I'm refreshing the data query (from sharepoint list). However this only happens far down the sheet.

I can write a VBA script to autofill each column from the top cell to fix this;

Range("A2").Select
Selection.AutoFill Destination:=Range("A2:A1555")
Range("B2").Select
Selection.AutoFill Destination:=Range("B2:B1555")

But this means repeating until column PD, a lot of typing.

Is there a more dynamic way to do this? The number of columns should never change, however the total number of rows likely will.

CodePudding user response：

You could do it like this in one operation:

Range("A2").Resize( , 420).AutoFill _
       Destination:=Range("A2").Resize(1554, 420)