Creating a macro to insert a blank row and total sums in a new column

**volleyballtigger** · 04-02-2013, 11:40 AM

Hi Everyone!

I'm very new to macros, and I have a project that I believe can really benefit from them, but I'm struggling to even know where to start. I've attached a snipit of what I'm working on so hopefully with that and my explanation, this will all make sense.

What I would like to do is get a blank row above each reference number that changes. So in the attached, I would want a blank row above row 2, row 5, row 8, row 12 and so forth. Then what I'm hoping to do is in column c, of that newly added blank row is to sum up the amounts in column b that correspond with the reference number. So for the first section I would want to get a total of $4783.61 in the newly created C2.

Then if any of you are really bored, the next step I'd like to take is using that sum ($4783.61) and then deducting each row again with that matching reference to C2 would be $4783.61, C3 would be $2512.63 and C4 would be 0.

Is this even the best way to do this?? I have 160,000+ records I'm working with so I'm looking for any help you can give me.

Thank you so much,

Heather

**FDibbins** · 04-02-2013, 11:53 AM

Hi and welcome to the forum

We would love to help you with your question, but 1st, please rename your thread to something more meaningful, that actually describes your problem.

Because thread titles are used in searching the forum it is vital they be written to accurately describe your thread content or overall objective using ONLY search friendly key words. That is, your title used as search terms would return relevant results.

Many members will look at a thread title, and if it is of interest to them, or it falls within their area of expertise, they might only open those threads.

Look at it this way...if you typed that title into google, what would you expect to get back?
To change a Title on your post, click EDIT on you're 1st post, then Go Advanced and change your title

**Leith Ross** · 04-02-2013, 04:26 PM

Hello volleyballtigger,

Welcome to the Forum!

I have added the macro below to the attached workbook and added a button on the worksheet to run it. It will add a blank line above each row where the reference numbers change and the declining balance for that reference will appear in column "C".

Please Login or Register  to view this content.

**volleyballtigger** · 04-22-2013, 10:05 AM

Leith-

Thank you so much for your help. I apologize for the late thank you, I've been without internet for a bit.

Now of course the people I'm working on this project for have changed what they are looking for. Here's how it looked originally.

123456 2,270.98 5,281.13
123456 2,512.63 2,768.50
123456 2,768.50 0.00

What they want instead is:

123456 2,270.98 7,552.11
123456 2,512.63 5,281.13
123456 2,768.50 2,768.50

So column C3 is now the Total of all the costs in column b that are in the same reference. Does this make sense? I've re-attaced the document you originally sent back to me.

Thank you again for your help!!

**Leith Ross** · 04-22-2013, 04:27 PM

Hello volleyballtigger,

Here is the updated macro and workbook. The new macro has been assigned to the button on "Sheet1".

Please Login or Register  to view this content.

**volleyballtigger** · 04-23-2013, 10:42 AM

Hi Leith,

Thank you again for your help. I'm having some issues, however. If I'm looking at this correctly, I need to run each macro. When I run the first one, it works as it did previously, but when I run the second, I'm getting a de-bug message and it is referencing Set Rng = .Range(FirstCell, .Cells(Rows.Count, "A").End(xlUp))

Any ideas here? So, right now what I'm getting in my results is still this:

123456 2,270.98 5,281.13
123456 2,512.63 2,768.50
123456 2,768.50 0.00

Instead of this:

123456 2,270.98 7,552.11
123456 2,512.63 5,281.13
123456 2,768.50 2,768.50