Thread: Macro to format the Auto-Sum row without knowing what row# it will be

Like so?

With Cells(Rows.Count, 1).End(xlUp)
    .Offset(, 6).Value = "Totals"
    .RowHeight = 33
    .EntireRow.HorizontalAlignment = xlCenter
    .Offset(, 6).Resize(, 2).HorizontalAlignment = xlCenterAcrossSelection
    .Resize(, 11).BorderAround Weight:=xlThick
End With

Hello, I got the following error:

Compile Error:

Only comments may appear after End Sub, End Function, or End Property.

on line 6.

Thanks

The code must be inside Sub/End Sub statements.

This formatted the row ABOVE the Auto-Sum row and not the Auto-Sum row itself.

Any thoughts?

Thanks

Rather than us to-ing and fro-ing why don't you make life easy and attach a small sample of your data as is?

Stephen,

Sorry, my company won't allow me to upload them at this time. I saw another post of mine regarding the auto-sum row and noticed this code...

rLastRw.Row+1

Would this be the code to use determine the autosum row?

Thanks much

Try changing to

With Cells(Rows.Count, 1).End(xlUp)(2)

Thanks much Stephen!

All worked except merging the auto-sum rows of F&G together. I spent about an hour trying to figure out the code you gave me to make some edits myself and had some luck (centering vertically, horizontally etc.).

The one thing I couldn't figure out was how to make the "G" Autosum row (the one that says "Totals") bold, size 12 and to merge "G" with "F".

Here's what I tried...

Sub Format_AutoSum_Row()
With Cells(Rows.Count, 1).End(xlUp)(2)
    .Offset(, 6)
    Selection.Font.Bold = True
    End With
    
End Sub

Plus merge the "G" auto-sum row with "F".

Thanks much

Try this. I think I got the wrong offset before.

Sub x()

With Cells(Rows.Count, 1).End(xlUp)(2)
    With .Offset(, 5)
        .Value = "Totals"
        .Font.Bold = True
        .Font.Size = 12
    End With
    .RowHeight = 33
    .EntireRow.HorizontalAlignment = xlCenter
    .Offset(, 5).Resize(, 2).HorizontalAlignment = xlCenterAcrossSelection
    .Resize(, 11).BorderAround Weight:=xlThick
End With

End Sub

Ahhh,

I think I'm getting the hang of this. I added the code

.Font.ColorIndex = 3

to your code and changed the font color to red on my own!

The code to merge the "F" and "G" Auto-Sum cell still isn't working though. I'm thinking that...

Selection.Merge

might need to be in there somewhere? Just a guess, you're the pro! What are your thoughts to merge F and G?

Thanks

You see, it's not rocket science.

It works for me - see attachment - although I used centre across selection rather than merge. Merging cells isn't recommended by anyone.

Stephen,

Hmm, very interesting about the merging thing. Okay, so to help me understand this code stuff a bit better, what exact line of code would you need to change to make this merge, rather than center across selection?

I did the macro recorder while merging 2 cells and it created this code

Selection.merge

Where exactly would you replace this (along with any other code) to get to merge rather than center across selection?

Knowing this helps me to decipher how you guys write all this stuff and 1 change like this helps me to understand the code writing process better. However, I still think that I will use your suggestion rather than merging.

ONE MORE THING...

I love the press me button that you created! Is there a way to get a button like that onto a toolbar as a button?

Thanks again

I reckon you could have figured that out yourself if you'd played a bit longer!

.Offset(, 5).Resize(, 2).Merge

I don't know about that particular design, but in general to assign a macro to a button, see here: http://www.ozgrid.com/Excel/assign-macros.htm

Alll Good, and solved!

Thanks again!