Clearing Blank (but not Empty) cells

**jon687** · 07-15-2009, 10:47 AM

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

**Palmetto** · 07-15-2009, 11:02 AM

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.

**jon687** · 07-16-2009, 03:51 AM

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

**DonkeyOte** · 07-16-2009, 05:56 AM

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.

**AGWSparks** · 01-04-2018, 11:41 AM

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.

**AliGW** · 01-04-2018, 12:09 PM

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