Need to Run Subtotal formula Top of the Column and run till end available data

[NasimAnsari]

Hello Everyone,

I would need help with the Subtotal formula to run the top column till end data with currency formula for specific columns.
Currently, I am using the below code which is not helping me

Sub Subtotal ()
Dim Lastrow As Long
Dim LastCol As Long
With Worksheets("Trending Report")
     Lastrow = .Cells(Rows.Count, "L").End(xlUp).Row
    .Range("L3:L" & Lastrow).Select
LastCol = ActiveSheet.Range("L3").End(xlToLeft).Column
ActiveSheet.Range("L3", ActiveSheet.Cells(Lastrow, LastCol)).Select
End With
With Selection
ActiveSheet.Range("L3", ActiveSheet.Cells(Lastrow, LastCol)).Select
Range("L3").Select
ActiveCell.FormulaR1C1 = "=SUBTOTAL(9,R[2]C:R[999998]C)"
Selection.Copy
Range("M3:P3").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    Selection.NumberFormat = "$#,##0.00_);[Red]($#,##0.00)"
Range("L3:P3").Select
Selection.Font.Bold = True
Selection.Interior.Color = RGB(219, 219, 219)
Selection.Font.Bold = True
Selection.Font.Size = 12
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
With Selection.Font
        .ColorIndex = xlAutomatic
    End With
    Range("L3").Select
End With
End With

 

[Fluff]

Hi & welcome to MrExcel.
Do you just want the formula in L3:P3, or should it be to the last col?

 

[NasimAnsari]

Hi & welcome to MrExcel.
Do you just want the formula in L3:P3, or should it be to the last col?

Yes, I would need VBA code to run my subtotal formula till data end.
I would be great help for me.

 

[NasimAnsari]

Hi & welcome to MrExcel.
Do you just want the formula in L3:P3, or should it be to the last col?

Hi Fluff.

Thank you for your reply and welcome greeting.?

 

[Fluff]

Ok, how about

Sub NasimAnsari()
   Dim Lastrow As Long
   Dim LastCol As Long
   With WorkSheets("Trending Report")
      Lastrow = .Cells(Rows.Count, "K").End(xlUp).Row
      LastCol = .Cells(4, Columns.Count).End(xlToLeft).Column
      .Range("L3", .Cells(3, LastCol)).FormulaR1C1 = "=SUBTOTAL(9,R5C:R" & Lastrow & "C)"
      .NumberFormat = "$#,##0.00_);[Red]($#,##0.00)"
      .Font.Bold = True
      .Interior.Color = RGB(219, 219, 219)
      .Font.Size = 12
      .HorizontalAlignment = xlGeneral
      .VerticalAlignment = xlCenter
      .WrapText = False
      .Orientation = 0
      .AddIndent = False
      .IndentLevel = 0
      .ShrinkToFit = False
      .ReadingOrder = xlContext
      .MergeCells = False
      .Font.ColorIndex = xlAutomatic
   End With
End Sub

 

[NasimAnsari]

Ok, how about

Sub NasimAnsari()
   Dim Lastrow As Long
   Dim LastCol As Long
   With WorkSheets("Trending Report")
      Lastrow = .Cells(Rows.Count, "K").End(xlUp).Row
      LastCol = .Cells(4, Columns.Count).End(xlToLeft).Column
      .Range("L3", .Cells(3, LastCol)).FormulaR1C1 = "=SUBTOTAL(9,R5C:R" & Lastrow & "C)"
      .NumberFormat = "$#,##0.00_);[Red]($#,##0.00)"
      .Font.Bold = True
      .Interior.Color = RGB(219, 219, 219)
      .Font.Size = 12
      .HorizontalAlignment = xlGeneral
      .VerticalAlignment = xlCenter
      .WrapText = False
      .Orientation = 0
      .AddIndent = False
      .IndentLevel = 0
      .ShrinkToFit = False
      .ReadingOrder = xlContext
      .MergeCells = False
      .Font.ColorIndex = xlAutomatic
   End With
End Sub

it's Working perfectly You are great Man... I must appreciate your work.
Thanks, Fluff and have a good day.?

 

[Fluff]

Glad to help & thanks for the feedback.