Macro to Auto Fill Down to last adjacent cells

Question

I want my macro to populate my formulas all the way down the sheet. Every time I run the macro it stops at row 13662 because that was the last row when I was recording it, but the last row changes constantly. Is there a way to do this? The columns I need to do this for are N, I, J, K, and L. Any help appreciated. See code below.

    Sub Weekly_Expiring_Rebate_Report()
'
' Weekly_Expiring_Rebate_Report Macro
'

'
    Columns("A:A").Select
    Range(Selection, Selection.End(xlToRight)).Select
    With Selection
        .VerticalAlignment = xlBottom
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    With Selection
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Columns("A:A").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Columns("A:Z").EntireColumn.AutoFit
    Columns("N:N").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.NumberFormat = "General"
    Range("N1").Select
    ActiveCell.FormulaR1C1 = "Expiring Rebate Status"
    Range("N2").Select
    Columns("N:N").EntireColumn.AutoFit
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],'Status IND'!C[-13]:C[-11],3,FALSE)"
    Range("N2").Select
    Selection.AutoFill Destination:=Range("N2:N13662")
    Range("N2:N13662").Select
    Columns("I:I").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("I2").Select
    ActiveCell.FormulaR1C1 = "=LEFT(RC[-1],4)"
    Range("I2").Select
    Selection.AutoFill Destination:=Range("I2:I13662")
    Range("I2:I13662").Select
    Range("J2").Select
    ActiveCell.FormulaR1C1 = "=LEFT(RC[-2],6)"
    Range("J2").Select
    Selection.AutoFill Destination:=Range("J2:J13662")
    Range("J2:J13662").Select
    Range("K2").Select
    ActiveCell.FormulaR1C1 = "=RIGHT(RC[-1],2)"
    Range("K2").Select
    Selection.AutoFill Destination:=Range("K2:K13662")
    Range("K2:K13662").Select
    Range("L2").Select
    ActiveCell.FormulaR1C1 = "=RIGHT(RC[-4],2)"
    Range("L2").Select
    Selection.AutoFill Destination:=Range("L2:L13662")
    Range("L2:L13662").Select
    Range("I1").Select
    ActiveCell.FormulaR1C1 = "EXP_YEAR"
    Range("K1").Select
    ActiveCell.FormulaR1C1 = "EXP_MONTH"
    Range("L1").Select
    ActiveCell.FormulaR1C1 = "EXP_DAY"
    Columns("I:L").Select
    Range("L1").Activate
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Columns("J:J").Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlToLeft
    Range("L1").Select
    ActiveCell.FormulaR1C1 = "EXP_Month_Name"
    Columns("I:L").Select
    Range("L1").Activate
    Columns("I:L").EntireColumn.AutoFit
    Range("L2").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-2],Month!C[-11]:C[-10],2,FALSE)"
    Range("L2").Select
    Selection.AutoFill Destination:=Range("L2:L13662")
    Range("L2:L13662").Select
    ActiveWindow.SmallScroll Down:=-6
    Columns("M:M").Select
End Sub

Sorry about that. I'm new to this, but I believe I fixed it. — PNigb, Oct 14 '16 at 19:09
Why can't you just drag on the bottom-right corner of the cell that has the formula and fill-down. In addition, you can see the N2:N13662, of course, it will stop there. Change that number (N13662). — Chai Xiong, Oct 14 '16 at 19:11
I could do that, but that would defeat the purpose of the macro wouldn't it? Next week it might need to be N2:N14000 or N2:N12345. I dont want to go in and change the number every time if there is a way to auto fill the formula to how many rows there are. — PNigb, Oct 14 '16 at 19:22

score 1 · Accepted Answer · edited May 23 '17 at 12:33

1

It looks like your issue is on the 5th row from the end.

Based on this answer you could replace:

Selection.AutoFill Destination:=Range("L2:L13662")

with

Selection.AutoFill Destination:=Range("L2:L" & ActiveSheet.UsedRange.Rows.Count)

edited May 23 '17 at 12:33

Community

1
1

answered Oct 14 '16 at 19:10

0w3n

325
1
9

Thanks for the response. I tried this and got the Run-time error '1004': Method 'Range' of object '_Global' failed – PNigb Oct 14 '16 at 19:55
@PNigb - Ah, I had a typo, deleted the "L" in the second part of the range. It runs without error for me, but without your input data i'm not 100% sure. I updated the answer above - it should be "L2:L" - you're only updating the row, not the column. – 0w3n Oct 14 '16 at 22:20
That worked like a charm! Thank so much for the help. – PNigb Oct 17 '16 at 11:57

XLmatters · Answer 2 · 2016-10-15T01:18:00.307

0

Here is an example of vba code that I think will work for your purposes.

'Counts the number of rows in column "C" and then places 
'concatenation formula in each adjacent cell (in column "D").
        Range("D2:D" & Range("C" & Rows.Count).End(xlUp).Row).Formula = "=CONCATENATE(C2,"", "",B2)"

edited Oct 15 '16 at 01:18

answered Oct 15 '16 at 01:12

XLmatters

376
1
16

Macro to Auto Fill Down to last adjacent cells

2 Answers2