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

Highlighted
Occasional Visitor

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 contain data in the cell next to it (Column E). Column E is the cell that the vlookup refers to.

 

The situation looks like this:

 

2017-12-14_09h25_21.png

 The code that I have so far looks like this:


    Sheets(3).Select
    Range("AY2").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-41],DennisAR!C[-50],1,0)"
    Selection.AutoFill Destination:=Range("AY2:AY1662")
    Range("AY2:AY1662").Select

 

The problem with this is, that the number of rows with data always change every week. So I cannot use a static row number of 1662.

 

I am looking for a way to make Destination:=Range("AY:AY1662) dynamic. In fact it has to refer to the number of rows with data in column E.

 

Thank you very much in advance,

Kai
   

 

 

62 Replies
Highlighted
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
Highlighted
Please also could you help me with the same for autofill ing down in series depending on data in the coloumn beside it
Highlighted

@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