asked on # Excel macro with range to last row

I have an excel macro as such:

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

```
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
```

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

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
```

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

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

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

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

Thanks

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

Change that parameter in the lastrow definition

LastR = Cells(Rows.Count, "f").End(xlUp).Row

T

Tried using A B and C but no luck.

Maybe it will help if I attach a small sample of starting point?

SampleGM.xlsx

Maybe it will help if I attach a small sample of starting point?

Get an unlimited membership to EE for less than $4 a week.

Unlimited question asking, solutions, articles and more.

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

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
Perfect - thanks!

Sub GMCalc()

'

' GMCalc Macro

'

'

Columns("F:F").Select

Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLe

Range("F1").Select

ActiveCell.FormulaR1C1 = "=IF(RC[-2]<>0,(RC[-2]-RC[

Dim LastR As Long

LastR = Cells(Rows.Count,"f").End(

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[

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