Forum Discussion

Copper Contributor

Oct 22, 2021

Solved

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

Hi, I am trying to get data from another document and save it another one. For example, getting data from Book 2 and saving it to Book 1. The issue is, this book 2 has 23,328 rows and now I have...

Oct 22, 2021

Replace

Range("R2").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-13],[OIH_Rec_Vendor_Funding_Deals_1_HARDLINES.xlsx]Deal_ASIN_Details!C11:C35,25,0)"
Range("R2").Select
Selection.AutoFill Destination:=Range("R2:R23328")
Range("R2:R23328").Select

with

    Dim m As Long
    m = Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Range("R2:R" & m).FormulaR1C1 = _
        "=VLOOKUP(RC[-13],[OIH_Rec_Vendor_Funding_Deals_1_HARDLINES.xlsx]Deal_ASIN_Details!C11:C35,25,0)"

HansVogelaar

MVP

Oct 22, 2021

KannanKumar

Replace

Range("R2").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-13],[OIH_Rec_Vendor_Funding_Deals_1_HARDLINES.xlsx]Deal_ASIN_Details!C11:C35,25,0)"
Range("R2").Select
Selection.AutoFill Destination:=Range("R2:R23328")
Range("R2:R23328").Select

with

    Dim m As Long
    m = Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Range("R2:R" & m).FormulaR1C1 = _
        "=VLOOKUP(RC[-13],[OIH_Rec_Vendor_Funding_Deals_1_HARDLINES.xlsx]Deal_ASIN_Details!C11:C35,25,0)"

KannanKumar

Copper Contributor

Oct 23, 2021

HansVogelaar Awesome, thanks for your quick response - this works perfectly!
If you could help me with another one, I will really appreciate it. After using the Macro, the final row in each sheets should be deleted automatically. Is there a way to do that? From the image I have provided, you can see the data is in R1738. But this row number will vary in all sheets however Data in the column E and Column R will remain the same for this row. How can I delete this entire row using Macro?