MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Filling blank cells with neighbouring data


Posted by Alex Golitsis on May 15, 2000 1:51 PM

Greetings !

Microsoft's mediocre spreadsheet is giving me grief, and I am looking for help !!

I have a downloaded report file that is missing some data. Basically, if the data is the same for more than row, the duplicated data is missing from the next rows (ie., Purchase Order number or invoice date for separate line items on one invoice).

Since Business Visions is even more unfriendly than Excel, I need to make Excel fill-in any blank cells with exact data found in the cells above any blanks.

I could have done this quite easily with Lotus 123 macros, but Excel's macros seem to put a literal twist on my intentions (for instance I am quite sure that a search for the first blank cell doesn't actually search for a blank cell when re-run, but instead drops the cursor on the EXACT cell that WAS blank when I created the macro... ridiculous).

Please let me know what I can do. If you have a suggestion involving Visual Basic, please do your best to provide working code as I have not yet dabbled in Excel's VBA.

Thanks for any help !!

Regards,
Alex Golitsis


Posted by Celia on May 15, 2000 3:06 PM

Alex
Select the whole range that contains the blanks and then run the following friendly code :-

Selection.SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"

To do it manually try the following friendly procedure :-
Select the whole range.
Select Edit>GoTo>Special>Blanks.
Assuming the active cell is then A2(say), input the formula =A1 into cell A2 and press Ctrl+Enter.

Celia

Posted by Alex Golitsis on May 19, 2000 5:36 AM

Many friendly thanks Celia !! Worx like a charm, you have saved me time and grief :>

thanks again,
Alex