Can this Macro be cleaned up?

[chriska416]

I have a macro that deletes the sales information at the end of the month so I can create a new sheet for the next month.

It works, and it works fine, it's just slow. I already turn off automatic calculation, and that helps to speed it up.

I do have a large number of formulas with nested ifs and vlookups pulling information that i need to process the commissions, so that may be what is causing it, and there is no other easier way.

At the very bottom of the data, i have the word "Finished" to signify the completion, since the number of total lines changes each month, and there is other necessary information below the totals that i don't want deleted. It's the only way I easily knew to make it stop.

Please Login or Register  to view this content.

It may be that nothing can be improved, and that's fine, but i thought I'd ask. I'm not exactly an expert in this arena.

Thanks for the help.

[dflak]

It's usually not a good idea to depend on activesheet or activecell since sometimes your idea of what's active and Excel's idea of what's active can be two different things. The issue you are having is doing things row-by-row. Whenever possible, work with entire ranges. This is what the attached code does, it goes down column C until it finds the word "Finished" and then deletes the entire range.

Please Login or Register  to view this content.

[chriska416]

Sorry, the problem is, i have many sales people on the sheet, each with a header row and subtotal.

I want to keep the header and subtotal, and only delete the sales information rows that change from month to month.

However, you did tun me onto the idea of deleting all their sales information at once for a single salesperson. i had not thought about that.

Now i just have to learn how to select all the rows from a set of data.

[dflak]

Without seeing the spreadsheet, I can only speculate on how your data is laid out. But working with ranges rather than cells or rows or columns is generally faster.

There are a couple of things to consider when looking for "boundaries" of your data. Helper Cells with MATCH() can really help out. Depending how you get your data, Excel Tables may also help. They automatically know what their boundaries are.

If you can provide a sample spreadsheet without sensitive data, maybe there is something I can clue in on to help you identify the ranges.

[Norie]

Couldn't you use a template sheet?

[chriska416]

Norie -

The sales people change so often, and there are so many other sheets that are tied to it, i wouldn't want to maintain a master as well as an ever-evolving monthly. in reality, i need some specialized commissions software. However, right now i'm leaps and bounds above where we were when i started. (1 sheet per person. manually entering all data). I'll get this company where it needs to go, but baby steps along the way.



Here is the updated code, and it is infinitely faster. and by infinitely, i mean there is definitely a finite amount of time that has been eliminated from the process (i'd say about 8-10 min), but it seems infinite.

Please Login or Register  to view this content.

dflak, thank you for making me thing differently.

[dflak]

Glad to be of help

[Norie]

I wasn't suggesting a 'master' sheet but a template sheet which has no sales data in it and can be copied whenever needed.

PS Your existing code would run quicker if you didn't use Select/Selection and, if possible, got rid of the looping.

[chriska416]

How would i go about removing the select/selection parts? what other options are there?