Forum Discussion

Copper Contributor

Dec 14, 2017

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

Hi ,
I have a situation where I have to autofill towards the right of like consider a cell E4 i have to autofill towards the right of it till it's below row has data.

Haytham Amairah

Silver Contributor

May 13, 2020

Thomas2170

Hi,

Please try this code:

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).FillRight
    Range(lastCellFromRight).Select
End Sub

But you need to select the starting cell before you run it which in the example below cell (E3):

Hope that helps

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

Thomas2170
Copper Contributor
May 19, 2020
Please also could you help me with the same for autofill ing down in series depending on data in the coloumn beside it
Thomas2170
Copper Contributor
May 18, 2020
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

Forum Discussion

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

Share

Resources