Avatar of Stief-Group
Stief-Group
Flag for Canada asked on

Excel macro with range to last row

I have an excel macro as such:

Sub GMCalc()
'
' GMCalc Macro
'

'
    Columns("F:F").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("F1").Select
    ActiveCell.FormulaR1C1 = "=IF(RC[-2]<>0,(RC[-2]-RC[-1])/RC[-2]*100,0)"
    Range("F1").Select
    Range("F2:F1016").Select
    Selection.FillDown
    Selection.NumberFormat = "0.00"
    Range("F1").Select
    ActiveCell.FormulaR1C1 = "Gross Margin YTD"
    Range("J1").Select
    ActiveCell.FormulaR1C1 = "Gross Margin LY YTD"
    Range("J1").Select
    ActiveCell.FormulaR1C1 = "=IF(RC[-2]<>0,(RC[-2]-RC[-1])/RC[-2]*100,0)"
    Range("J1").Select
    Range("J:J").Select
    Selection.FillDown
    Columns("J2:J1016").Select
    Selection.NumberFormat = "0.00"
    Selection.Font.Bold = True
    Range("J1").Select
    ActiveCell.FormulaR1C1 = "Gross Margin LY YTD"
End Sub

Open in new window


I'd like to get it so that the two ranges (F:F1016 and J2:J1016) will dynamically select the range up to the last used row.

How do I accomplish this?

Thanks
Microsoft Excel

Avatar of undefined
Last Comment
Stief-Group

8/22/2022 - Mon
Patrick Matthews

Try:

Sub GMCalc()

'
' GMCalc Macro
'

'
    Columns("F:F").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("F1").Select
    ActiveCell.FormulaR1C1 = "=IF(RC[-2]<>0,(RC[-2]-RC[-1])/RC[-2]*100,0)"

    Dim LastR As Long
    LastR = Cells(Rows.Count,"f").End(xlUp).Row
    Range("F2:F" & LastR).Select

    Selection.FillDown
    Selection.NumberFormat = "0.00"
    Range("F1").Select
    ActiveCell.FormulaR1C1 = "Gross Margin YTD"
    Range("J1").Select
    ActiveCell.FormulaR1C1 = "Gross Margin LY YTD"
    Range("J1").Select
    ActiveCell.FormulaR1C1 = "=IF(RC[-2]<>0,(RC[-2]-RC[-1])/RC[-2]*100,0)"
    Range("J1").Select
    Range("J:J").Select
    Selection.FillDown
    Columns("J2:J1016").Select
    Selection.NumberFormat = "0.00"
    Selection.Font.Bold = True
    Range("J1").Select
    ActiveCell.FormulaR1C1 = "Gross Margin LY YTD"
End Sub


nutsch

you forgot one, Patrick

Sub GMCalc()

'
' GMCalc Macro
'

'
    Columns("F:F").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("F1").FormulaR1C1 = "=IF(RC[-2]<>0,(RC[-2]-RC[-1])/RC[-2]*100,0)"

    Dim LastR As Long
    LastR = Cells(Rows.Count, "f").End(xlUp).Row
    With Range("F2:F" & LastR)
        .FillDown
        .NumberFormat = "0.00"
    End With
    Range("F1").FormulaR1C1 = "Gross Margin YTD"
    Range("J1").FormulaR1C1 = "Gross Margin LY YTD"
    Range("J1").FormulaR1C1 = "=IF(RC[-2]<>0,(RC[-2]-RC[-1])/RC[-2]*100,0)"
    With Range("J2:J" & LastR)
        .FillDown
        .NumberFormat = "0.00"
        .Font.Bold = True
    End With
    Range("J1").FormulaR1C1 = "Gross Margin LY YTD"
End Sub

Open in new window

Patrick Matthews

Crikey, so I did :)

That's what I get for trying to do the minimum, instead of showing the Asker the right way to do this :)
Your help has saved me hundreds of hours of internet surfing.
fblack61
Stief-Group

ASKER
I tried to use the macro you provdied, nutsch, but all I ended up with was the first row?  F3/J3 on didn't get the formula "filled down" at all.

I see I tagged this as Excel 2007, when in fact, I'm running 2010.  Could this be the problem?

Thanks
nutsch

Which column has the data that's filled? You want to extend the formula all the way down, but all the way down to match which column? A? B?

Change that parameter in the lastrow definition
LastR = Cells(Rows.Count, "f").End(xlUp).Row

T
Stief-Group

ASKER
Tried using A B and C but no luck.

Maybe it will help if I attach a small sample of starting point?
SampleGM.xlsx
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER CERTIFIED SOLUTION
nutsch

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Stief-Group

ASKER
Perfect - thanks!