Delete Blank Cells but Hold Values for Calculations

[vba_help]

Hi all,

I have 50 000+ lines of data, and thus, would like to learn of an easier solution to what I have been doing in the past week or so. In short, I need a formula or VBA macro that removes the whole row of data if there is a blank in one of the cells; however, I need Excel to "remember" or hold the value that it deletes and use this on a simple calculation for the remaining cells which were not deleted.

In column B = dates of the event, column D = string (with some cells being blank), column E = integer values. (Columns A and C are irrelevant to what I need to do). I would like Excel to find the blank cells in column D, hold the numerical value (column E), delete the whole row and redistribute the held value to the remaining cells corresponding to a particular date.

For example,

........A ................B.............. C .....................D........... .......E
1 ..Description ......Date ........Description_2....... Code.............Value
2 .....Taxi.......... 31/07/2006.... Work............ ..ALZ..............0.00765
3 .....Taxi ..........31/07/2006 ....Work .............CRTXXL .........0.63400
4 .....Taxi ..........31/07/2006 ....Hometime .......WOT3 ...........0.645483
5..... Taxi.......... 31/07/2006 .....Work ................................0.210
6 .....Taxi ..........30/04/2008 .....Hometime .......PTYR ...........0.765

....etc etc.... for 50 000+ lines.

I want Excel to identify that there is a blank in cell D5, delete the whole of row 5 but hold the value 0.210 in its memory, divide 0.210 by the remaining number of cells corresponding to that particular date (ie. 0.210/3) and add this amount (0.07) to each the values in cells E2, E3, E4.... and repeat for all the remaining blanks.

I would also like Excel to output in a new worksheet all the rows that it has deleted (ie. all the information in row 5 in the above example), so that I can double-check it.

I hope Excel is able to do this...as I have been manually calculating this over the past week or so.


Thank you in advance for any help and assistance. It will make my life so much more easier.

[snb]

Did you consider the following sequence?

first: do the arithmatic
second: delete rows ?

[foxguy]

Hi vba_help;

Just letting you know. Yes Excel VBA can do what you want. I'm working on it right now.
I may not finish it tonight, and I may not be able to work on it in the morning, but I should be able to finish it by tomorrow night.
Obviously, I won't be able to test it unless you upload a file for me to test it on.

[foxguy]

Hi vba_help;

I'm attaching a file that has a macro in it. The macro is getting too big to post, so I'm uploading the file it's in.

This first draft just determines what rows need to be deleted and copies them into another sheet. Check it out and see if it's correct so far.

If you don't know how to get my macro into your workbook:
1) Open both workbooks
2) Click Alt+F11 to open VBE
3) Click Ctrl+R to open Project Explorer
4) Find my workbook. It will have this name : "VBAProject(Delete Blanks And Hold.xls)"
5) If there is a "+" sign next to it, then click the "+" sign to expand the project.
6) Find your workbook. If necessary expand it also
7) Find "Module 1" in my workbook and drag and drop it onto your workbook (it doesn't matter where you drop it, it will get put in the right place by the VBE).

In the macro, change the values in the section marked by '*********************

I can't work any more tonight, so I'll work some more tomorrow.