Put dummy data in all empty cells (MS Excel 2000)

Topic

New Reply

I have a worksheet that stores 11 columns of data relating to bookings on a daily basis over a 12-month period. There can be multiple lines for a particular day and not all the cells have a data entry. A sample is attached. For another process, extracting the data from a copy of the file saved in CSV format using the PHP function fgetcsv, I need to put dummy data in the empty cells. Otherwise the function fails at the first empty cell in each row.

How can I get enter e.g. a full stop in all empty cells with a formula or perhaps a macro? Your help would be very much appreciated.

Reply | Quote

Replies

(Edited by sdckapr on 28-Mar-08 05:24. Added PS)

You can use a line of code like:
Cells.SpecialCells(xlCellTypeBlanks).Formula = “Dummy”

To put the literal text “Dummy” into all the blanks

Steve

PS manually you can do this by selecting the region
Edit – go to – special
select blanks
[ok]
Enter the dummy string

Reply | Quote

Select the range in which you want to enter data into blank cells, e.g. A1:K33.
Select Edit | Go To… or press Ctrl+G.
Click Special…
Select Blanks.
Click OK.
Type some text, e.g. a full stop, or a formula such as =RAND()
Press Ctrl+Enter to enter the value in all blank cells.

Note: a few cells will appear to remain blank, for example F8 and J32. These cells contain a single space and therefore Excel doesn’t consider them to be blank.

Reply | Quote

Steve and Hans,

The short time taken for your replies to my enquiry has me overawed. Do the timings of 13 and 14 minutes indicate competition or coincidence? {8;-)) Whatever I am truly grateful for the procedures you have given me.

And I have a supplementary. As the working worksheet has of the order of 600 rows is there a way to ensure apparently empty cells are indeed blank?

Reply | Quote

It was just coincidence.

In post 698,173 you’ll find a macro that clears all cells within the selection that look blank.

Reply | Quote

Hans, Thank you

Reply | Quote

Timings of 13 and 14 minutes indicate to me that they are both slacking on the job

(Only joking, as I hope they appreciate!)

Reply | Quote