Adjusting Macro to be more dynamic for additional columns

Hey, below is the code I have to combine entries that have the same column A & B combination. It is based on the fact that I have 11 columns of data (13 columns total). I was wondering if there is a way to make it more dynamic, so if there are more or less columns of data it can adjust for that. I have marked the areas that would need to be changed.


What the Macro does is basically do a SUMIFS statement to the right of the data, copy the values back and then erase duplicate rows,
A:M is the 13 original columns (2 title columns, 11 data columns), I'm thinking something like a column count (if that exists?)
N is the first free column (13 + 1) (column count plus 1)
X = (13+13-2) (column count, plus column count, minus 2)

Please Login or Register  to view this content.

Thanks for the help!

A sample workbook to go with would really help.

Maybe this will do what you want, stab in the dark:

Please Login or Register  to view this content.

Heh, deleting an unknown number of rows all at once COULD be far faster than one by one deletions, so now you see another technique. Whenever I do mass deletions, this is what I use currently out of habit.

General rule of thumb I try to go by...touch the sheet itself as infrequently as possible. That's why I also tried to show you have to isnert your formula directly into your target range instead of fillright, fill down.

I can't recommend any particular book, I use the internet and online resources for researching/learning.

If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.

Originally Posted by minimacros

Sorry to revive an old thread, I was just curious why you set the DelRNG in beginning and how you know this won't erase the last row?

Please Login or Register  to view this content.

It absolutely WILL erase the last row. I've seldom seen data ranges that fill the entire spreadsheet all the way to the last row where the OP didn't mention that fact, it's usually already presented a whole slough of additional problems needing solving, too.

So, 99 times out of 100 this is a safe technique.

Why do I do it? It's a personal preference. I call it 'seeding' a deletion-range with the first value from a cell outside the data range. The point of the one line of code is to keep the Set DelRNG command that comes later to a simple UNION() method, possible because the DelRNG already exists.

If I didn't do that, then inside EVERY loop I would be testing to see if the delRNG exists or not before adding to it, which is potentially 1000s of additional calculations. I can eliminate all of those by seeding the range once before I start into the loop.

As I said, it's an efficiency/personal preference thing.