Fix Blank Excel Cells Copied From Database

When you copy data to Excel, from another application, blank cells in the data can cause problems. Everything looks okay, at first glance, but the database blank cells don't behave like other blank cells in the workbook.

In this example, there is a small table, copied from Access, with 3 cells – C4, C5 and C7 – that appear blank. However, the COUNTA formula in cell E2 is counting those cells, as if they contained data.


Fix the Problem

To fix the problem, so the "blank" cells are recognized as blank, you can use the Replace command, in two steps.

  • Replace all the blanks with a string that is not in the data, such as "$$$$"
    • FixBlankCells02
  • Replace the "$$$$" string with an empty string
    • FixBlankCells03

You can record a macro while you fix the cells, and run that macro later, if you encounter a similar problem.

The Fix Blank Cells Code

Here is the Excel VBA code that fixes the blank cells, by using the Replace command. It works on the selected cells in the active worksheet.

Sub FixBlankCells()
' Make blank cells from database really blank
    With Selection
        .Replace What:="", _
            Replacement:="$$$$", _
            LookAt:=xlPart, _
            SearchOrder:=xlByRows, _
            MatchCase:=False, _
            SearchFormat:=False, _
        .Replace What:="$$$$", _
            Replacement:="", _
            LookAt:=xlPart, _
            SearchOrder:=xlByRows, _
            MatchCase:=False, _
            SearchFormat:=False, _
    End With
End Sub

Watch the Fix Blank Cells Video

To see the steps for fixing the blank cells, and recording and running a macro, you can watch this short Excel video tutorial.

Watch on YouTube: Fix Blank Cells in Excel

Download the Sample Workbook

To work with the sample tables, and run the macro, you can download the Fix Blank Cells in Excel sample workbook. The file is in Excel 2007 format, zipped, and contains macros. You'll have to enable macros to test the Fix Blank Cells code in the sample file.


You may also like...

8 Responses

  1. Ed Ferrero says:

    I like using Text to Columns to get rid of blanks. Just select a column, click Text to Columns, check delimited, and click Finish.

  2. Gobish says:

    I use Auto filter to filer for blank cells and then delete the contents of the blank cells. Normally this works fine for me. Thanks for the Find and replace option, i have not tried it that way.

  3. Nice trick Debra! I used to use the method told by Gobish….

  4. John Kyle says:

    Debra, this is a nice little macro for cleaning up blank cells. I like it because this macro can be part of an excel automation solution that pulls data from a database, loads data into an Excel report, cleans it with your macro and then distributes the final report to users. Thanks for the helpful tip!

  5. Gregory says:

    You would think that since Microsoft makes both products they would have fixed this noxious problem. It can be a real pain in the you-know-what the first time you come across it. Thanks for the quick and efficient solution.

  6. Gregory says:

    I found that the following code works equally well (but probably not as fast):

    Sub FixBlankText()
    Dim cel As Range
    For Each cel In Selection
    If Len(cel) = 0 And VarType(cel) = vbString Then
    cel = 0
    cel = “”
    End If
    Next cel
    End Sub

  7. Marcos says:

    This was the best, simple, fast, elegant solution I’ve found so far. We don’t need any fancy coding. The simple, the better. Thank you, whoever you are!

Leave a Reply to Gregory Cancel reply

Your email address will not be published. Required fields are marked *