Macro for sum up and sorting

shawrod

Active Member
Joined
Oct 1, 2010
Messages
399
I am looking for a simple macro that sums up all cells on C and enter result on the last empty cell of C column of the worksheet and same thing with columns D-H. On the last cells of Columns G and H that has the result , set cell format to $. Then I would like to select the whole data on the worksheet except for the last row and sort it by column I from largest to smallest. Can anyone help?
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Yes I have but the problem is my data is not always the same size and macro recorder would have worked if my data was the same size. I will be adding this formula to personal macro.
 
Upvote 0
Try this on a copy of the worksheet
Code:
Sub sumall()    Dim wf As WorksheetFunction
    Dim was As Worksheet
    
    Set wf = Application.WorksheetFunction
    Set ws = ActiveWorkbook.ActiveSheet
    
    For I = 3 To 8 'selecting column C to column H
        lastrow = Cells(Rows.Count, I).End(xlUp).Row 'finds the lastrow
        Cells(lastrow + 1, I).Value = wf.Sum(Range(Cells(1, I), Cells(lastrow, I))) 'puts the sum on the first empty cell
        If I = 7 Or I = 8 Then
            Cells(lastrow + 1, I).Select
            Selection.NumberFormat = "$#,##0.00"
        End If
    Next I
    
    'This is the sort portion of the macro, from the macro recorder
    
    ws.Sort.SortFields.Clear
    ws.Sort.SortFields.Add Key:=Range("A1"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ws.Sort
        .SetRange Range("A1:H" & lastrow)
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub
 
Upvote 0
Thank you so much. This worked I had to make a few changes. I needed to sort based on I2 so I changed it to
Range ("I2")
I needed sorted from largest to smallest so I changed it to:

Order:=xlDescending

I wanted the sum result go to the last row of data and overwrite what's in there so I changed it to:

Cells(lastrow, I).Value

I wanted to select row 2 to one row before the last line to sort a I have title in the row 1 an total in the last row :

.SetRange Range("A2:I" & lastrow -1)

So overall it looks like this:
[Dim was As Worksheet

Set wf = Application.WorksheetFunction
Set ws = ActiveWorkbook.ActiveSheet


For i = 3 To 8 'selecting column C to column H
lastrow = Cells(Rows.Count, i).End(xlUp).Row 'finds the lastrow
Cells(lastrow, i).Value = wf.Sum(Range(Cells(1, i), Cells(lastrow, i))) 'puts the sum on the first empty cell
If i = 7 Or i = 8 Then
Cells(lastrow, i).Select
Selection.NumberFormat = "$#,##0.00"
End If
Next i

'This is the sort portion of the macro, from the macro recorder

ws.Sort.SortFields.Clear
ws.Sort.SortFields.Add Key:=Range("I2"), _
SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
With ws.Sort
.SetRange Range("A2:I" & lastrow - 1)
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub]

Also one thing I like to ask you is what is the code to select the last row of the data. I can use macro recorder to do the rest for me if I have the code for selecting the last row of the data.
 
Upvote 0
something like
Code:
Range("A" & Rows.Count).End(xlUp).Row
will give you the last row of data in column A
 
Upvote 0
Where and how are using in it in the code?
 
Upvote 0
I am using it on Personal macro right after the above code you wrote for me. I want to apply it to this code that I got from macro recorder:

Code:
 Range("A10:J10").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorLight2
        .TintAndShade = 0.799981688894314
        .PatternTintAndShade = 0
    End With
    With Selection.Font
        .Name = "Calibri"
        .FontStyle = "Bold"
        .Size = 11
        
    End With
    Range("C10").Select
    ActiveCell.FormulaR1C1 = "=COUNT(R[-8]C:R[-1]C)"
    Range("A10").Select
    ActiveCell.FormulaR1C1 = ""
    Range("A10").Select
    ActiveCell.FormulaR1C1 = "TOTAL: "
    With ActiveCell.Characters(Start:=1, Length:=7).Font
        .Name = "Calibri"
        .FontStyle = "Bold"
        .Size = 11
        
    End With
     Range("A16").Select 
End Sub
 
Last edited:
Upvote 0
You said "you get a compile row". i wanted to know on what line of code you were getting the error, so as to help troubleshoot
 
Upvote 0

Forum statistics

Threads
1,213,496
Messages
6,113,993
Members
448,539
Latest member
alex78

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