CODEWITHSUNDEEP
vbaexcelmacros
PNigb
PNigb

Reputation: 21

Macro to Auto Fill Down to last adjacent cells

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

Upvotes: 0

Views: 4941

Answers (2)

XLmatters
XLmatters

Reputation: 386

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)"

Upvotes: 0

0w3n
0w3n

Reputation: 335

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)

Upvotes: 1

Related Questions