+ Reply to Thread
Results 1 to 6 of 6

Clearing Blank (but not Empty) cells

  1. #1
    Registered User
    Join Date
    07-15-2009
    Location
    dfgdg
    MS-Off Ver
    Excel 2003
    Posts
    2

    Clearing Blank (but not Empty) cells

    In Excel there is a difference between cells that are blank (= "") and that are empty. You can make blank cells empty by selecting them and pressing the delete key, but I need a fast way to do this on all blank cells in a large worksheet. The only way I have found is with a VBA macro that loops through every cell, tests for '.Value = "" ' and then uses the '.Clear()' function, but doing this on 30 columns x 10000 rows is far too slow. Any solutions?

    (The reason I need to do this is for importing into Access, the database treats empty cells as NULL which is what I want. Blank (but not empty) cells screw the import process up.)

  2. #2
    Forum Expert Palmetto's Avatar
    Join Date
    04-04-2007
    Location
    South Eastern, USA
    MS-Off Ver
    XP, 2007, 2010
    Posts
    3,978

    Re: Clearing Blank (but not Empty) cells

    One non-VBA approach:

    Copy the worksheet, values only to another sheet.
    On the original sheet, use the select all and press the delete key, then copy the values (only) back into the original sheet.

  3. #3
    Registered User
    Join Date
    07-15-2009
    Location
    dfgdg
    MS-Off Ver
    Excel 2003
    Posts
    2

    Re: Clearing Blank (but not Empty) cells

    This does not work. Paste Special with values only preserves cells that are blank but not empty, at least on Excel 2003 & 2007.

  4. #4
    Forum Moderator DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Suffolk, UK
    MS-Off Ver
    2002, 2007 & 2010
    Posts
    21,520

    Re: Clearing Blank (but not Empty) cells

    Is it viable to run an Update SQL command against your Access Table to change "" entries to [Null] post update ? By the sound of it you would never have a valid blank value in your Access table.

    Unfortunately there's no easy way in VBA to simultaneously select & clear all Nulls given they are Text equiv.

  5. #5
    Registered User
    Join Date
    01-04-2018
    Location
    Cape
    MS-Off Ver
    10&13
    Posts
    1

    Re: Clearing Blank (but not Empty) cells

    Had the same problem as jon687 but when screen updating etc were switched off the speed increased sufficiently:

    Hope this helps.

    Please Login or Register  to view this content.
    Last edited by AGWSparks; 01-04-2018 at 04:10 PM. Reason: Added code format tags

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Excel 2013 (Win 10 - Work) & 2016 365 Subscription (Win 10 - Home)
    Posts
    16,577

    Re: Clearing Blank (but not Empty) cells

    Thanks for this. However, two things:

    1. This thread is 9 years old.
    2. Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

    Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

    Click on Edit to open your thread, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    If anyone has helped you towards solving a problem, then you can thank them by clicking on their reputation star bottom left.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1