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

[duugg]

I currently have a macro setup that puts Auto-Sum data in the row below the bottom-most row of data. What code would I need to format this Auto-Sum row with the following parameters automatically...

1. Merge & center F & G in the Auto-Sum row with the text "Totals".
2. Change the row height of the Auto-Sum row to 44 pixels.
3. Center the vertical alignment of the Auto-Sum row.
4. Put the thickest border around the Auto-Sum row in cells A-K.

Thanks much

[StephenR]

Like so?

Please Login or Register  to view this content.

[duugg]

Hello, I got the following error:

Compile Error:

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

on line 6.

Thanks

[StephenR]

The code must be inside Sub/End Sub statements.

[duugg]

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

Any thoughts?

Thanks

[StephenR]

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?

[duugg]

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...

Please Login or Register  to view this content.

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

Thanks much

[StephenR]

Try changing to

Please Login or Register  to view this content.

[duugg]

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...

Please Login or Register  to view this content.

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

Thanks much

[StephenR]

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

Please Login or Register  to view this content.

[duugg]

Ahhh,

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

Please Login or Register  to view this content.

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...

Please Login or Register  to view this content.

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

Thanks

[StephenR]

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.

[duugg]

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

Please Login or Register  to view this content.

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

[StephenR]

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

Please Login or Register  to view this content.

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

[duugg]

Alll Good, and solved!

Thanks again!