CODEWITHSUNDEEP
excelvba
Wayne_Chen
Wayne_Chen

Reputation: 31

How to Dynamically Autofill Cells

I would like to dynamically fill a column. However, with the VBA code I have now, the formula fills all the way to the max row count (1.04 million). I would like it to stop at the max rows found in the column to its left (same as manual auto fill) 

Sub Vlookup()

Range("C2").Select
ActiveCell.FormulaR1C1 = 
"=IFERROR(VLOOKUP(RC[-2],'C:\Test.xlsx'!R2C6:R1000C15,10,FALSE),"""")"
Selection.AutoFill Destination:=Range("C2", Range("C2").End(xlDown))

End Sub

I tried to use 

Selection.Autofill Destination := 
Range("C2",Selection.Range("C2").End(xldown))

But that didnt seem to work either

Upvotes: 1

Views: 641

Answers (2)

Darrell H
Darrell H

Reputation: 1886

You always want to use Long when finding the last row. Integers only go to 32,767 and there are over 1 million rows. You should also avoid using Select.

Dim lastRow as Long
lastRow=Cells(Rows.Count,2).End(xlup).Row

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

Upvotes: 1

jonrizz
jonrizz

Reputation: 94

first you can get the last row coming from column B and use that as reference for auto fill. see my example below.

Dim lastRow As Integer
Range("B1").Select

Selection.End(xlDown).Select
lastRow = Selection.Row
Range("C2").Select
Selection.AutoFill Destination:=Range("C2:C" & lastRow)

Upvotes: 0

Related Questions