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]

Welcome to Board, Will.
AFAIK, one can't Autofill rows AND columns simultaneously. It is not possible manually and hence probably not possible in code either.
You have to first Autofill column and then rows. Or vice versa.
Thus:

Sub FillRange()
LastCol = Cells(2, Columns.Count).End(xlToLeft).Column
LastRow = Cells(Rows.Count, "B").End(xlUp).Row
Range("C3").AutoFill Destination:=Range("C3:C" & LastRow), Type:=xlFillDefault 'fill C column
Range("C3:C" & LastRow).AutoFill Destination:=Range(Cells(3, "C"), Cells(LastRow, LastCol)), Type:=xlFillDefault 'fill all rows
End Sub

 

[AlphaFrog]

To add to drsarao's excellent answer, you could fill a formula in both columns and rows using .FormulaR1C1

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

This method only fills the formula. No formatting is filled as with the .Autofill method.

 

[drsarao]

AlphaFrog, Cool! Thanks.

Just .Formula also works. Why .FormulaR1C1? Just habit?

Range("C3", Cells(LastRow, LastCol)).Formula = Range("C3").Formula

 

[AlphaFrog]

Why .FormulaR1C1? Just habit?

Yes.

 

[willwill88]

Re: VBA Autofill Last Row & Column- Dynamic Range- SOLVED

Drsarao & AlphaFrog thank you both for your answers.

Drsarao- Thank you for the excellent response and welcoming. Your explaination was clear and the VBA code worked perfectly.

AlphaFrog- One thing I forgot to mention is that the formula is an array formula, so I wasn't able to get use the technique successfully.

After searching other threads, I was able to piece together a different solution. I think that I'll stick with Drsarao's code since it is simple and short. Thank you both for your assistance!

'   Find the last filled row in column B
Dim lRow As Long
lRow = Cells(Rows.Count, 2).End(xlUp).Row
 
'   Find the last filled column in row 2
Dim lCol As Long
lCol = Cells(2, Columns.Count).End(xlToLeft).Column
 
'   Fill from C3 to last row of data from Column B.
Range("C3").Select
Selection.AutoFill Destination:=Range("C3:C" & Range("B1000000").End(xlUp).Row), Type:=xlFillDefault
 
'   Fill C3:C"Last Row" to last column from Row 2
Range("C3", Cells(lRow, 3)).Select
Selection.AutoFill Destination:=Range(Cells(3, lCol), Cells(lRow, 3)), Type:=xlFillDefault

 

[drsarao]

You are welcome.

I notice, you use ".Select" a lot in your code. When we record a macro, Excel provides this type of code. Because it is recording whatever you are doing.

However, it is NOT efficient and significantly slow in execution. Whenever Macro accesses Excel sheet (.select being the most common) there is a huge time penalty. (You may not notice it in small Subs, but becomes significant in larger codes).

Usually ".Select" and "Selection." (in the following line) can be deleted to merge both lines in one.

Only upside of sticking with Select is that you get to watch the code do what you would have done manually. Exactly. If that pleases your Karma, then so be it. Time be damned!!

 

[BuddJ]

How do I go about using this code with an Array Formula ??

Many thanks

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

 

[drsarao]

Use .FormulaArray instead of .FormulaR1C1

Check here: http://www.mrexcel.com/forum/excel-...pplications-how-place-array-formula-cell.html

 

[BuddJ]

Thanks for the reply drsarao. One small question / issue though.........

When I use .FormulaR1C1 then as the formula is propogated across the range the Column & Row values change accordingly, as would be seen in a copy / paste function. However when I replace .FormulaR1C1 with .FormulaArray the Column & Row values that propogate remain the same as if I had used a $A$1 type functionality to freeze the source.

Any ideas as to why that is, or howto resolve??

Here is my array formula :-

=INDEX(Working!$A$5:$R$5000,MATCH(1,(Working!$A$5:$A$5000=$A7)*(Working!$D$5:$D$5000=E$2),0),18)

The one set of reference data is located in Column A with the second located in Row 2, so for those I freeze the Column & Row respectively. The result with .FormulaArray is every single cell has the exact same formula / result as the above code instead of referencing the relevant source. eg :-
A7 & E2 for the above,
A6 & D2
A5 & C2, etc.