troubleshooting Question

Excel macro with range to last row

Avatar of Stief-Group
Stief-GroupFlag for Canada asked on
Microsoft Excel
8 Comments1 Solution639 ViewsLast Modified:
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

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
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 8 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 8 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros