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?
 

Some videos you may like

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

Momentman

Well-known Member
Joined
Jan 11, 2012
Messages
4,066
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
Have you tried the macro recorder?
 

shawrod

Active Member
Joined
Oct 1, 2010
Messages
399
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.
 

Momentman

Well-known Member
Joined
Jan 11, 2012
Messages
4,066
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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
 

shawrod

Active Member
Joined
Oct 1, 2010
Messages
399

ADVERTISEMENT

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.
 

Momentman

Well-known Member
Joined
Jan 11, 2012
Messages
4,066
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
something like
Code:
Range("A" & Rows.Count).End(xlUp).Row
will give you the last row of data in column A
 

shawrod

Active Member
Joined
Oct 1, 2010
Messages
399

ADVERTISEMENT

I get "compile error" It says .Row is invalid use of property. .
 

Momentman

Well-known Member
Joined
Jan 11, 2012
Messages
4,066
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
Where and how are using in it in the code?
 

shawrod

Active Member
Joined
Oct 1, 2010
Messages
399
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:

Momentman

Well-known Member
Joined
Jan 11, 2012
Messages
4,066
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,207
Messages
5,594,841
Members
413,944
Latest member
3xc3ln00b

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
Top