Procedure too large when running a macro

[Finalfrontier1976]

Hi all,

I've attached my example spreadsheet to show what I'm trying to do.

On Sheet 2 is the raw data. What I have had to do is record 2 Macros to try and achieve what I want but when running macro Format2 I'm getting an error message saying the "Procedure is too large". Macro Format1 does the initial piece of formatting I want but ideally I'd want one macro to do it all.

What I want to do exactly is this:

Dump raw data into the spreadsheet (exactly like what is in Sheet 2 but the amount of lines will differ so I don't want anything set in regards to how much to select).

Then delete columns I-U: inclusive
Delete rows 1-9 inclusive
Remove the column merge on columns A-B
Then remove column B
Then auto width the column of A so that it is long enough to see all the names
Remove column merge on D-E
Remove column E and auto width it then centre everything in that column
Remove line 2 (which is blank)

Sheet 1 shows the end product

Any suggestions would be great,
Thanks,
Lew

[bakerman2]

Let's start with this.

Please Login or Register  to view this content.

[Roel Jongman]

This is an example where the macro recorder does not work to well what it does is change the cell format cell by cell since you selected col A and B it looks like it is trying to do it 2 miljon times or so. it might even qualify as a bug of the macro recorder to do it this way.

Your request is not that exotic and requires only simple code
I think below macro does all you ask above.

Please Login or Register  to view this content.

note: I choose to build the macro based on your listed actions.
It could be made a little more efficient to VBA coding standards. But that won't affect speed much. This is easier to understand what each line of code does exactly.

[Finalfrontier1976]

That is brilliant, thank you very much!