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

Thomas2170

Copper Contributor

Thanks@Haytham,

But i m facing one issue that it is autofilling as 1 I want it be filled in series. Like 1,2,3 and so on

Haytham Amairah

Silver Contributor

May 23, 2020

Thomas2170

Hi,

Please use this below instead, and tell me what you think.

Sub AutoFillToRight()
    Dim lastCellFromRight As String
    ActiveCell.Select

    lastCellFromRight = Selection.End(xlDown).End(xlToRight).Offset(-1, 0).Address
    
    If lastCellFromRight = "$XFD$1048575" Or lastCellFromRight = "$IV$65535" Then Exit Sub
    
    Range(ActiveCell.Address, lastCellFromRight).FillUp
    Range(lastCellFromRight).Select
End Sub

Regards

Forum Discussion

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

Share

Resources