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 “$$$$”
- Replace the “$$$$” string with an empty string
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, _ ReplaceFormat:=False .Replace What:="$$$$", _ Replacement:="", _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ MatchCase:=False, _ SearchFormat:=False, _ ReplaceFormat:=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.