VBA Autofill Last Row & Column- Dynamic Range

[willwill88]

I'm working on a VBA that will autofill a formula (cell C3) based on the last row in column B, and last column in row 2.

The number of rows and columns is dynamic, however the formula will always be in C3.

This part works... but it only fills the first row and column:

LastCol = Range("C2").End(xlToRight).Column
LastRow = Range("B3").End(xlDown).Row
Debug.Print LastRow, LastCol

Range("C3").AutoFill Destination:=Range("C3", Cells(3, LastCol)), Type:=xlFillDefault
Range("C3").Select

Range("C3").Select
Selection.AutoFill Destination:=Range("C3:C" & Range("B1000000").End(xlUp).Row), Type:=xlFillDefault


I tried using the code below to fill the entire "rectangle" but I'm getting an error:


Range("C3").AutoFill Destination:=Range(Cells(LastRow, 3), Cells(3, LastCol)), Type:=xlFillDefault



Any ideas on how I can make this work???

Thanks!

 

[drsarao]

The OP too was using an Array formula and faced the same problem.
Maybe it is designed that way for some reason and it may not be possible to propagate relative references in Array formulae.
The other way to do it is by using VBA to do the autofill manually as illustrated in post #2. You can modify and try.
Not as elegant as AlphaFrog's solution but should work for you.

 

[MARK858]

The OP too was using an Array formula and faced the same problem.
Maybe it is designed that way for some reason and it may not be possible to propagate relative references in Array formulae.
The other way to do it is by using VBA to do the autofill manually as illustrated in post #2. You can modify and try.
Not as elegant as AlphaFrog's solution but should work for you.

@drsarao, can you compare the results you are getting with your code with the code below

Sub FillRange()
    Dim LastCol As Long, LastRow As Long
    LastCol = Cells(2, Columns.Count).End(xlToLeft).Column
    LastRow = Cells(Rows.Count, "B").End(xlUp).Row


    With Range(Cells(3, "C"), Cells(LastRow, LastCol))
        .Formula = Range("C3").Formula
        .FormulaArray = .FormulaR1C1
    End With
End Sub

 

[drsarao]

Mark,
It works exactly as intended!
Cool workaround.
Budd should adopt this - this would be significantly faster on large ranges.
Thanks.

 

[AgniArul]

I have created vba to creatable from the consolidated data in a sheet1. After a table structure is created, the macro will populate the values based on the formula.

in my macro there are 3 tables. the 1st table is getting created with appropriate values based on the formula. during the process od 2nd and 3rd table, formula which is applied for grandtotal sums the above table. This is the area where the error is starting and proceeds the same for the remaining tables.

I don't have any issues when this macro is run in my system. when this gets run on the other system, this error occurs. Any fix for this. EXCEL 2013

Below is the sample::
SUB UPDATE()


Range("C16").Select
ActiveCell.FormulaR1C1 = _
"=COUNTIFS('Con_DATA'!C4,RC2,'Con_DATA'!C[10], ""laNE"")"
Selection.AutoFill Destination:=Range("C16:C22"), Type:=xlFillDefault
Range("C16:C22").Select


Range("D16").Select
ActiveCell.FormulaR1C1 = _
"=COUNTIFS('Con_DATA'!C4,RC2,'Con_DATA'!C[9], ""MED"")"
ActiveWorkbook.Save
Selection.AutoFill Destination:=Range("D16:D22"), Type:=xlFillDefault
Range("D16:D22").Select
Range("E16").Select
ActiveCell.FormulaR1C1 = _
"=COUNTIFS('Con_DATA'!C4,RC2,'Con_DATA'!C[8], ""SIM"")"
ActiveWorkbook.Save
Selection.AutoFill Destination:=Range("E16:E22"), Type:=xlFillDefault
Range("E16:E22").Select
Range("F16").Select
ActiveCell.FormulaR1C1 = _
"=COUNTIFS('Con_DATA'!C4,RC2,'Con_DATA'!C[7], ""TAPE`enter code here`"")"
ActiveWorkbook.Save
Selection.AutoFill Destination:=Range("F16:F22"), Type:=xlFillDefault
Range("F16:F22").Select
Range("G16").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-4]:RC[-1])"
ActiveWorkbook.Save
Selection.AutoFill Destination:=Range("G16:G22"), Type:=xlFillDefault
Range("G16:G22").Select
Range("C23").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-7]C:R[-1]C)"
ActiveWorkbook.Save
Selection.AutoFill Destination:=Range("C23:G23"), Type:=xlFillDefault
Range("C23:G23").Select
ActiveWorkbook.Save
End Sub