VBA Selection.Autofil Dynamic Range for columns & rows

[Busybeaver]

Hi,

I've read a number of posts regarding autofill for dynamic ranges but they all refer to number of rows. Is there a way to also calculate a varying number of columns please? (PS: I love this site, I almost always find the help I need by reading through the threads!)

I have a dataset that will change number of both columns and rows. Under each column header is a formula. What I am trying to do is mimic the action of highlighting existing columns and autofilling the formulae down to the last row.

Range("B2").Select
Range(Selection, Selection.End(xlToRight)).Select

' This next part doesn't work because obviously it is only looking at column M and not all highlighted columns. Can I also make the column reference dynamic?
Selection.AutoFill Destination:=Range("M2:M" & Range("A" & Rows.Count).End(xlUp).Row)

Below is a sample of the sheet. Row 2 holds the formulae.

 

[kevin9999]

Try the following on a copy of your workbook - just change the sheet name to suit.

Option Explicit
Sub Flexible_Autofill()
    Dim ws As Worksheet
    Set ws = Worksheets("Sheet1")   '<~~ *** Change sheet name to suit ***
    Dim LRow As Long, LCol As Long, i As Long
    LRow = ws.Cells(Rows.Count, "A").End(xlUp).Row
    LCol = ws.Cells(2, Columns.Count).End(xlToLeft).Column
    
    For i = 2 To LCol
        ws.Cells(2, i).AutoFill ws.Range(ws.Cells(2, i), ws.Cells(LRow, i))
    Next i
End Sub

 

[Busybeaver]

Dim ws As Worksheet Set ws = Worksheets("Sheet1") '<~~ *** Change sheet name to suit *** Dim LRow As Long, LCol As Long, i As Long LRow = ws.Cells(Rows.Count, "A").End(xlUp).Row LCol = ws.Cells(2, Columns.Count).End(xlToLeft).Column For i = 2 To LCol ws.Cells(2, i).AutoFill ws.Range(ws.Cells(2, i), ws.Cells(LRow, i)) Next i

Works Perfectly, thank-you so much

 

[kevin9999]

Happy to help & thanks for the feedback

 

[Peter_SSs]

Why not do all the columns at once instead of one-by-one?

Sub Flexible_Autofill_v2()
  Dim ws As Worksheet
  Dim LRow As Long, LCol As Long
  
  Set ws = Worksheets("Sheet1")   '<~~ *** Change sheet name to suit ***
  LRow = ws.Cells(Rows.Count, "A").End(xlUp).Row
  LCol = ws.Cells(2, Columns.Count).End(xlToLeft).Column
  ws.Cells(2, 2).Resize(LRow - 1, LCol - 1).FillDown
End Sub