Macro for sum up and sorting

[shawrod]

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?

 

[shawrod]

Right at ".Row" at the end of this code: Range("A" & Rows.Count).End(xlUp).Row

 

[Momentman]

Please post the code as you have written it and highlight the line in question 'cos i cant seem to see this line in the code you posted

 

[shawrod]

.Row in Bold

Range("A" & Rows.Count).End(xlUp ).[B]Row [/B]
Selection.RowHeight = 39
  
    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
  
End Sub

 

[Sahil123]

Hi,

i think you will need to assign it to a variable

try

dim lrow as longlrow = Range("A" & Rows.Count).End(xlUp ).Row

 

[Momentman]

Sure you can just say

lastrow = Range("A" & Rows.Count).End(xlUp ).Row

and refer to it using lastrow subsequently

 

[shawrod]

Not it says "syntax error" on this new line! And the line is in red.

 

[shawrod]

lastrow = Range("A" & Rows.Count).End(xlUp ).Row

The above code worked but it didn’t exactly do what I wanted. It only highlighted the last cell on H where it should have changed the format of all last cells from A-I. Any Idea why?

 

[Momentman]

Maybe if you could just explain your request, we could help with a code. But in any case to change the number format for the lastrow on columns A-L I use something like this that loops from column to column

For I = 1 To 10   
    lastrow = Cells(Row.Count, I).End(xlUp).Row
    Cells(lastrow, I).Select
    Selection.NumberFormat = "$#,##00.00"
Next I

 

[shawrod]

What I am trying to do is. To resize the height of the last rows (A-I) of the data to 39. Then Bold the font and fill in the color that I have selected. Does it make sense?

 

[Momentman]

Something like dis maybe

For K = 1 To 10    
    lastrow = Cells(Row.Count, K).End(xlUp).Row
    Cells(lastrow, K).Select
    
    Selection.RowHeight = 39
  
    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
    
Next K