- last edited on
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:
The code that I have so far looks like this:
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-41],DennisAR!C[-50],1,0)"
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,
05-18-2020 07:46 AM
05-19-2020 03:41 AM
05-23-2020 05:27 AM
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