Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    May 2003
    Location
    Mid-Cheshire, England
    Posts
    275
    Thanks
    1
    Thanked 3 Times in 3 Posts

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

    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.
    Attached Files Attached Files

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

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

    <P ID="edit" class=small>(Edited by sdckapr on 28-Mar-08 05:24. Added PS)</P>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
    <ctrl><enter>

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

    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.

  4. #4
    3 Star Lounger
    Join Date
    May 2003
    Location
    Mid-Cheshire, England
    Posts
    275
    Thanks
    1
    Thanked 3 Times in 3 Posts

    Re: Put dummy data in all empty cells (MS Excel 20

    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?

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Put dummy data in all empty cells (MS Excel 20

    It was just coincidence.

    In <post:=698,173>post 698,173</post:> you'll find a macro that clears all cells within the selection that look blank.

  6. #6
    3 Star Lounger
    Join Date
    May 2003
    Location
    Mid-Cheshire, England
    Posts
    275
    Thanks
    1
    Thanked 3 Times in 3 Posts

    Re: Put dummy data in all empty cells (MS Excel 20

    Hans, Thank you

  7. #7
    Plutonium Lounger Leif's Avatar
    Join Date
    Dec 2000
    Location
    U.K.
    Posts
    14,010
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Put dummy data in all empty cells (MS Excel 20

    Timings of 13 and 14 minutes indicate to me that they are both slacking on the job <img src=/S/laugh.gif border=0 alt=laugh width=15 height=15>

    (Only joking, as I hope they appreciate!)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •