Forum Discussion
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 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
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").Selectto:
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
83 Replies
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.
@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!!
' 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()))
Hi
If you wee to create a Table instead, Insert > Table, then your formula would automatically be inserted as you add rows to the Table, without the need for any VBA code.
Replace these lines of code:
Selection.AutoFill Destination:=Range("AY2:AY1662") Range("AY2:AY1662").SelectWith this:
Selection.AutoFill Destination:=Range("AY2:AY" & Range("E" & Rows.Count).End(xlUp).Row) Range(Selection, Selection.End(xlDown)).SelectGive it a try!
And provide me with any feedback!
Hey Haytham Amairah
I know this is an old post, but i hope that you have an solution for my problem
I want to make a new line for this one wich works fineSelection.AutoFill Destination:=Range("C2:C11"), Type:=xlFillFormats
Range("C2:C11").Select
Selection.Cut
Range("B2").Select
ActiveSheet.Paste
Selection.TextToColumns Destination:=Range("B2"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:="|", FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
---------------------------------------------
But when i use this because of i also want to autofill a row until the end of the number of data in another row:Selection.AutoFill Destination:=Range("C2:C" & Range("E" & Rows.Count).End(xlUp).Row)
Range(Selection, Selection.End(xlDown)).Select
Selection.Cut
Range("B2").Select
ActiveSheet.Paste
Selection.TextToColumns Destination:=Range("B2"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:="|", FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
Al the info in line B and C is the same as in B2 and C2
I hope you can help me.
Thanks for reading thisThank You Sooooo MUCH!! :)
I created an account just to thank you, it worked perfectly !
