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

[Kai El Harrar]

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:

 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
   

[#2]

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

[#3]

Please also could you help me with the same for autofill ing down in series depending on data in the coloumn beside it

[#4]

@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

[#5]

Hi,
What will be the Range (e.g. AY2:AY) if the data is auto filtered prior to autofill and the first cell in column AY is dynamic i.e. not static AY2?

[#6]

@W4rcloud Buggy code. The xldown trick does not work in your example because all cells below selection are empty. Please test your code before posting. Thanks. 

' 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()))

[#7]

' 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()))

[#8]

I want to use the same code, but i need to fill multiple coloums. Eg D2:AF2 with reference A

[#9]

Worked perfect! Thanks!@Haytham Amairah 

[#10]

@Haytham

I apologize that I am not able to find what I need among so much work toward this subject...please help.

I have a workbook that has 4 sheets, all tables. I need to be able to enter a new line of information in the first table (a non-formulated table) and then have all the other tables drag down formulas to match the number of data rows in the first table... How do I get this to do this automatically? What would be the code on the button to get this to happen? Any help would be appreciated!!

[#11]

@Haytham Amairah 

I would like your help and I think I tagged the first post incorrectly. Thanks!

[#12]

@Haytham Amairah 

Hi Just wanted to know whether we can have the variable for the first row in AY2. So instead of 2 can we add some variable at the beginning & replace that variable at the code as my first row is not fixed.

[#13]

@Haytham Amairah 

Hello Haytham,

I am have a similar issue. I need the formula to fill if the columns and rows have data. Due to the rows and columns fluctuating periodically and daily. The formula will be in D7.

Thank you for your time.

[#14]

What's for the "E" ?

[#15]

How do you lookup then copy and paste data when there's previous data from the above cell. My file is being updated daily so there's new data. I need to make a VBA that looks up the new data then copy and paste it as values. 

1. I need to look up the last entry on Column C on Sheet1 then enter the data on Column B (IFERROR(INDEX(Sheet2!B:B,MATCH(Sheet1!B:B,Sheet2!A:A,0)),"") on Sheet2 

2. Autofill data until the last row

3. Copy and paste as values

4. Run again once there's new data on 

I don't know what code to use on the autofill part. 

[#16]

Hi

I tried the same for simple macro but it didn't work.

Can pls you help me

[#17]

Hi, thank you for sharing your wisdom.

It worked for a while until lately i found out it occurred error if there is only one row,

and i dont know how to correct it.

Would you please me how?

Thank you.

@Haytham Amairah 

[#18]

@Haytham Amairah 

I want to do a similar thing but I want to copy the formula in cell AY2 down for the length of the column E, not autofill.

Thanks

[#19]

@haytham I somewhat have the same issue however am doing index-match instead of vlookup. Solution is the same hence am very grateful that I stubble upon this tread

[#20]

i have two sheets where if the heading of a column matches with the value in another sheet q column then vlook up is executed. if it is false the it will jump to next heading this sequence goes on for 8 times .

but in third row though it is matching with the heading the we lookup formula is identifing a273 as 103642 where as it is 066-50007-1531

=IFERROR(IF(ac_matched!$B$1=removals_only!Q273,VLOOKUP(removals_only!A273,ac_matched!$J:$J,1,0),IF(ac_matched!$C$1=removals_only!Q273,VLOOKUP(removals_only!A273,ac_matched!$K:$K,1,0),IF(ac_matched!$D$1=removals_only!Q273,VLOOKUP(ac_matched!A273,ac_matched!$L:$L,1,0),IF(ac_matched!$E$1=removals_only!Q273,VLOOKUP(removals_only!A273,ac_matched!$M:$M,1,0),IF(ac_matched!$F$1=removals_only!Q273,VLOOKUP(removals_only!A273,ac_matched!$N:$N,1,0),IF(ac_matched!$G$1=removals_only!Q273,VLOOKUP(removals_only!A273,ac_matched!$O:$O,1,0),IF(ac_matched!$H$1=removals_only!Q273,VLOOKUP(removals_only!A273,ac_matched!$P:$P,1,0),IF(ac_matched!$I$1=removals_only!Q273,VLOOKUP(removals_only!A273,ac_matched!$Q:$Q,1,0),"")))))))),"nhpn")

i have this problem with vlook up hope you have solution to it.

[#21]

@Haytham Amairah hi I ran a macro to sort a file, the last step is to sort data by "sales unit this year "means data from Column  H must Goto Column  G. to do this  i used sort function to move all the data to column G now to move the remaining data from H to G I went to the last cell of G that has data , move one cell below it to the empty cell the use a formula to bring data from H(e.g lastE

mptycellOF G=H56)  and tried to autofill this to the equivalent  last H cell that has data

this is the code that i added on top of the quote that was recorded by VBA when i was recoding a macro

Range("A4").Select
Selection.End(xlToRight).Select
Range("G4").Select
Selection.End(xlDown).Select
Range("G1059044").Select 'go all the way down to cell 1059044
Selection.End(xlUp).Select 'then co the way up until you find data
lastCell = ActiveCell.Address(True, False) 'trying to give the lastcell the address of the active cell because last cell always changes according to the length of the data
ActiveCell.Offset(1, 0).Select 'from the active cell which is supposed to be the last cell go one cell down
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=RC[1]"
Selection.AutoFill Destination:=Range("lastCell" & Range("G4" & Rows.Count).End(xlUp).Row) ' want it to select the dynamic cell and autofill the formula
Range(Selection, Selection.End(xlDown)).Select