Use VBA to Autofill a Row until the end of the number of data in another row

Hello, I need some help with the following problem: The Macro should select the first cell with the vlookup (AY2) and autofill the complete range in the column AY until the last row that ...

Macros & VBA

Requests

Liabilityquek
Mar 25, 2019
Haytham Amairah

Hi Haytham Amairah,

I was facing similar issues and chanced upon this thread. If i have two columns (O and P) that i wish to autofill via VBA, do I amend the code from:

Selection.AutoFill Destination:=Range("O2:P313")
Range("O2:P313").Select

to:
Selection.AutoFill Destination:=Range("O2:O" & Range("E" & Rows.Count).End(xlUp).Row)
Range(Selection, Selection.End(xlDown)).Select

Selection.AutoFill Destination:=Range("P2:" & Range("E" & Rows.Count).End(xlUp).Row)
Range(Selection, Selection.End(xlDown)).Select

Haytham Amairah

Silver Contributor

Dec 14, 2017

Replace these lines of code:

Selection.AutoFill Destination:=Range("AY2:AY1662")
Range("AY2:AY1662").Select

With this:

Selection.AutoFill Destination:=Range("AY2:AY" & Range("E" & Rows.Count).End(xlUp).Row)
Range(Selection, Selection.End(xlDown)).Select

Give it a try!

And provide me with any feedback!

jrsnijder

Copper Contributor

Mar 11, 2025

Hey Haytham Amairah
I know this is an old post, but i hope that you have an solution for my problem
I want to make a new line for this one wich works fine

Selection.AutoFill Destination:=Range("C2:C11"), Type:=xlFillFormats

Range("C2:C11").Select

Selection.Cut

Range("B2").Select

ActiveSheet.Paste

Selection.TextToColumns Destination:=Range("B2"), DataType:=xlDelimited, _

TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _

Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _

:="|", FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True

---------------------------------------------
But when i use this because of i also want to autofill a row until the end of the number of data in another row:

Selection.AutoFill Destination:=Range("C2:C" & Range("E" & Rows.Count).End(xlUp).Row)

Range(Selection, Selection.End(xlDown)).Select