Autofill several columns using a marco

**adx1000** · 09-12-2011, 06:57 PM

Hi everyone.

I have been pulling my hair out over this one. Just cant figure it out.

I am trying to autofill a formula down a column, as far as there is data in the adjacent row [ Or the length of column A] in a macro.

The idea is that i paste my values into column A, and run a macro that autofills the formulas in B2,C2,D2.....O2 down as far as there are values in column A.

At the moment i am having to paste my values into column A, and double click all the auto fill handles on the cells with the formulas.

I have tried this

Dim i As Long
i = Cells(Rows.Count, "A").End(xlUp).Row
Selection.AutoFill Destination:=Range("B2:B" & i)
Selection.Autofill Destination:=Range("C2:C" & i)
etc to
Selection.Autofill Destination:=Range("O2:O" & i)

This works for the B column, but fails from there.

Any ideas ? I really need help !

**FloMM2** · 09-12-2011, 09:39 PM

adx1000,
Could you supply a dummy worksheet? It will be easier to answer.
hth

**mudraker** · 09-13-2011, 12:17 AM

The following comand will fail if the selected cell is not in column C

Selection.Autofill Destination:=Range("C2:C" & i)

try

Range("c1").AutoFill Destination:=Range("C2:C" & i)

or to autofill multiple adjoining columns

Range("c1:e1").AutoFill Destination:=Range("C2:e" & i)

**adx1000** · 09-13-2011, 06:53 AM

Originally Posted by mudraker

The following comand will fail if the selected cell is not in column C

try

Range("c1").AutoFill Destination:=Range("C2:C" & i)

or to autofill multiple adjoining columns

Range("c1:e1").AutoFill Destination:=Range("C2:e" & i)

Many thanks Mudraker. I had to use a combo of the two above to get the macro to run. Otherwise the selection was too large.

I ended up with

Sub autofillcolumns()

Dim i As Long


Application.ScreenUpdating = False
        i = Cells(Rows.Count, "A").End(xlUp).Row
        
      Range("B2:E2").AutoFill Destination:=Range("B2:E" & i)
      Range("F2").AutoFill Destination:=Range("F2:F" & i)
      Range("G2").AutoFill Destination:=Range("G2:G" & i)
      Range("H2").AutoFill Destination:=Range("H2:H" & i)
      Range("I2").AutoFill Destination:=Range("I2:I" & i)
      Range("J2").AutoFill Destination:=Range("J2:J" & i)
      Range("K2").AutoFill Destination:=Range("K2:K" & i)
      Range("L2").AutoFill Destination:=Range("L2:L" & i)
      Range("M2").AutoFill Destination:=Range("M2:M" & i)
      Range("N2").AutoFill Destination:=Range("N2:N" & i)
      Range("O2").AutoFill Destination:=Range("O2:O" & i)

End Sub

Which works like a charm. Again, many thanks.
So did my original macro fail due to cell selection ?