Forum Discussion

Kai El Harrar's avatar
Kai El Harrar
Copper Contributor
Dec 14, 2017
Solved

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's avatar
    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

     

OlivierPlanchon's avatar
OlivierPlanchon
Copper Contributor
Nov 03, 2020

' version 1

Sub AutoFill_lengthy_self_explanatory()

    Dim startcell, endcell As Range

    Set startcell = Selection.Range("A1")

    Set endcell = startcell.Offset(0, -1).End(xlDown).Offset(0, Selection.Columns.Count())

    Selection.AutoFill Destination:=Range(startcell, endcell)

End Sub

 

' version 2: same but one-liner that you can use instead of a function:

Selection.AutoFill Destination:=Range(Selection.Range("A1"), Selection.Range("A1").Offset(0, -1).End(xlDown).Offset(0, Selection.Columns.Count()))

 

Resources