Using macro to sum dynamic range with result in cell 2 below last row of dynamic range

A

AvoidingVBA

New Member
Joined
Dec 15, 2021
Messages
7
Office Version
  1. 365
Platform
  1. Windows
I'm trying to take a recorded macro which results in absolute ranges and apply dynamic ranges to the code. In this case, I need to sum a dynamic range and then have the result appear two cells below the last row of the dynamic range.

In this example, I would need to sum the totals of A1:A4 and have the result appear in A6. The number of rows will change with each dataset but the columns will not.
1639589828695.png


I've already had wonderful help from these forums so there will be a mix of recorded and VBA. Please note this task is just a part of a larger macro, and due to my unfamiliarity with VBA I'm not 100% sure where these specific inputs start and end, but it is after the AutoFill and before the Sort commands. In this code, the column that holds the range I will need to tabulate is E. I will then have to replicate the same tabulation in the four adjacent cells (F,G,H,I). Here is the current code:

Range("A11").Select
Application.CutCopyMode = False
Selection.AutoFill Destination:=Range("A11:A" & Range("E" & Rows.Count).End(xlUp).Row)
Range("A11:A54").Select
Range("I11").Select
Selection.AutoFill Destination:=Range("I11:I" & Range("E" & Rows.Count).End(xlUp).Row)
Range("I11:I54").Select
Range("E11").Select
Selection.End(xlDown).Select
Range("E56").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=SUM(R[-46]C:R[-2]C)"
Range("E56").Select
Selection.Copy
Range("E56:I56").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("K56").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=RC[-6]-RC[-2]"
Range("A10:J54").Select
Range("E25").Activate
ActiveWorkbook.ActiveSheet.Sort.SortFields.Clear
ActiveWorkbook.ActiveSheet.Sort.SortFields.Add2 Key:=Range( _
"A11:A54"), SortOn:=xlSortOnValues, Order:=xlAscending, CustomOrder:= _
"Equity,Allocation,Unknown,BDC,Fixed,Cash/Equiv", DataOption:=xlSortNormal
ActiveWorkbook.ActiveSheet.Sort.SortFields.Add(Range("B11:B54"), _
xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(0, 176 _
, 240)
ActiveWorkbook.ActiveSheet.Sort.SortFields.Add(Range("B11:B54"), _
xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(231, _
230, 230)
ActiveWorkbook.ActiveSheet.Sort.SortFields.Add2 Key:=Range( _
"B11:B54"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.ActiveSheet.Sort
.SetRange Range("A10:J54")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
ActiveWindow.SmallScroll Down:=-72
Range("A4:B8").Select
End Sub
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
Sort by date Sort by votes
In future when posting code, please use code tags. How to Post Your VBA Code

How about
VBA Code: 
Sub AvoidingVba()
   Dim UsdRws As Long
   
   UsdRws = Range("E" & Rows.Count).End(xlUp).Row
   Range("A11").AutoFill Destination:=Range("A11:A" & UsdRws)
   Range("I11").AutoFill Destination:=Range("I11:I" & UsdRws)
   Range("E" & UsdRws + 2).Resize(, 5).FormulaR1C1 = "=SUM(R11C:R[-2]C)"
   Range("K" & UsdRws + 2).FormulaR1C1 = "=RC[-6]-RC[-2]"
   ActiveSheet.Sort
      .SortFields.Clear
      .SortFields.Add2 Key:=Range( _
         "A11:A54"), SortOn:=xlSortOnValues, Order:=xlAscending, CustomOrder:= _
         "Equity,Allocation,Unknown,BDC,Fixed,Cash/Equiv", DataOption:=xlSortNormal
      .SortFields.Add(Range("B11:B54"), _
         xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(0, 176 _
         , 240)
      .SortFields.Add(Range("B11:B54"), _
         xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(231, _
         230, 230)
      .SortFields.Add2 Key:=Range( _
           "B11:B54"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
           xlSortNormal
         .SetRange Range("A10:J54")
      .Header = xlYes
      .MatchCase = False
      .Orientation = xlTopToBottom
      .SortMethod = xlPinYin
      .Apply
    End With
    ActiveWindow.SmallScroll Down:=-72
    Range("A4:B8").Select
End Sub
 
Upvote 0
Fluff said:
In future when posting code, please use code tags. How to Post Your VBA Code

How about
VBA Code: 
Sub AvoidingVba()
   Dim UsdRws As Long
 
   UsdRws = Range("E" & Rows.Count).End(xlUp).Row
   Range("A11").AutoFill Destination:=Range("A11:A" & UsdRws)
   Range("I11").AutoFill Destination:=Range("I11:I" & UsdRws)
   Range("E" & UsdRws + 2).Resize(, 5).FormulaR1C1 = "=SUM(R11C:R[-2]C)"
   Range("K" & UsdRws + 2).FormulaR1C1 = "=RC[-6]-RC[-2]"
   ActiveSheet.Sort
      .SortFields.Clear
      .SortFields.Add2 Key:=Range( _
         "A11:A54"), SortOn:=xlSortOnValues, Order:=xlAscending, CustomOrder:= _
         "Equity,Allocation,Unknown,BDC,Fixed,Cash/Equiv", DataOption:=xlSortNormal
      .SortFields.Add(Range("B11:B54"), _
         xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(0, 176 _
         , 240)
      .SortFields.Add(Range("B11:B54"), _
         xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(231, _
         230, 230)
      .SortFields.Add2 Key:=Range( _
           "B11:B54"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
           xlSortNormal
         .SetRange Range("A10:J54")
      .Header = xlYes
      .MatchCase = False
      .Orientation = xlTopToBottom
      .SortMethod = xlPinYin
      .Apply
    End With
    ActiveWindow.SmallScroll Down:=-72
    Range("A4:B8").Select
End Sub
Click to expand...
Thank you for the tagging tip, as you can tell I'm new around here.

When I attempt to run this code I get an error "Compile Error: Invalid or unqualified reference"

The text ".sortfields" in line 7 is highlighted when VBA editor opens to display the error (bolded below).


VBA Code: 
   UsdRws = Range("E" & Rows.Count).End(xlUp).Row
   Range("A11").AutoFill Destination:=Range("A11:A" & UsdRws)
   Range("I11").AutoFill Destination:=Range("I11:I" & UsdRws)
   Range("E" & UsdRws + 2).Resize(, 5).FormulaR1C1 = "=SUM(R11C:R[-2]C)"
   Range("K" & UsdRws + 2).FormulaR1C1 = "=RC[-6]-RC[-2]"
   ActiveSheet.Sort
      .SortFields.Clear
      .SortFields.Add2 Key:=Range( _
 
Upvote 0
Oops, missed a With, it should be
VBA Code: 
Sub AvoidingVba()
   Dim UsdRws As Long
   
   UsdRws = Range("E" & Rows.Count).End(xlUp).Row
   Range("A11").AutoFill Destination:=Range("A11:A" & UsdRws)
   Range("I11").AutoFill Destination:=Range("I11:I" & UsdRws)
   Range("E" & UsdRws + 2).Resize(, 5).FormulaR1C1 = "=SUM(R11C:R[-2]C)"
   Range("K" & UsdRws + 2).FormulaR1C1 = "=RC[-6]-RC[-2]"
   With ActiveSheet.Sort
      .SortFields.Clear
      .SortFields.Add2 Key:=Range( _
         "A11:A54"), SortOn:=xlSortOnValues, Order:=xlAscending, CustomOrder:= _
         "Equity,Allocation,Unknown,BDC,Fixed,Cash/Equiv", DataOption:=xlSortNormal
      .SortFields.Add(Range("B11:B54"), _
         xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(0, 176 _
         , 240)
      .SortFields.Add(Range("B11:B54"), _
         xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(231, _
         230, 230)
      .SortFields.Add2 Key:=Range( _
           "B11:B54"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
           xlSortNormal
         .SetRange Range("A10:J54")
      .Header = xlYes
      .MatchCase = False
      .Orientation = xlTopToBottom
      .SortMethod = xlPinYin
      .Apply
    End With
    ActiveWindow.SmallScroll Down:=-72
    Range("A4:B8").Select
End Sub
 
Upvote 0
Solution
Fluff said:
Oops, missed a With, it should be
VBA Code: 
Sub AvoidingVba()
   Dim UsdRws As Long
  
   UsdRws = Range("E" & Rows.Count).End(xlUp).Row
   Range("A11").AutoFill Destination:=Range("A11:A" & UsdRws)
   Range("I11").AutoFill Destination:=Range("I11:I" & UsdRws)
   Range("E" & UsdRws + 2).Resize(, 5).FormulaR1C1 = "=SUM(R11C:R[-2]C)"
   Range("K" & UsdRws + 2).FormulaR1C1 = "=RC[-6]-RC[-2]"
   With ActiveSheet.Sort
      .SortFields.Clear
      .SortFields.Add2 Key:=Range( _
         "A11:A54"), SortOn:=xlSortOnValues, Order:=xlAscending, CustomOrder:= _
         "Equity,Allocation,Unknown,BDC,Fixed,Cash/Equiv", DataOption:=xlSortNormal
      .SortFields.Add(Range("B11:B54"), _
         xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(0, 176 _
         , 240)
      .SortFields.Add(Range("B11:B54"), _
         xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(231, _
         230, 230)
      .SortFields.Add2 Key:=Range( _
           "B11:B54"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
           xlSortNormal
         .SetRange Range("A10:J54")
      .Header = xlYes
      .MatchCase = False
      .Orientation = xlTopToBottom
      .SortMethod = xlPinYin
      .Apply
    End With
    ActiveWindow.SmallScroll Down:=-72
    Range("A4:B8").Select
End Sub
Click to expand...
Thank you, this works perfectly!
 
Upvote 0
You're welcome & thanks for the feedback.
 
Upvote 0
You must log in or register to reply here.

Similar threads

Dazzawm
  • Question Question
Change Recorded Range To Used Range.
2
Replies
12
Views
870
Dazzawm
Dazzawm
P
  • Solved
Macro to scroll to the last coloured cell in a spreadsheet
Replies
4
Views
376
Joe4
Joe4
C
  • Question Question
How to get my macro to stop at the last recognized line
Replies
6
Views
533
Coco1
C
O
  • Solved
VBA: Deleting Visible rows using Auto Filter; Excluding Header
Replies
4
Views
806
mumps
mumps
C
  • Solved
How to not set a range in VBA
Replies
7
Views
614
Coco1
C

Forum statistics

Threads
1,223,236
Messages
6,170,915
Members
452,366
Latest member
TePunaBloke

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top