MeraWindows Forums - Get your Computer Problems Fixed

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(s) 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.

Hi Ashish,

I have uploaded one of the workbooks for you here ---> http://www.mediafire.com/?j2yodj1njzt

The blank cells are in column D and the value I need Excel to hold is in column E of the same row if the cell in column D is blank.

Thanks so much again!