Remove Spaces From Empty Cells

We put information from all types of sources into our Excel workbooks, usually just by copying and pasting or by using another program to extract data from a database. In either case, sometimes this produces undesired results. Cells that may appear empty really aren’t, as they will often times contain spaces. Usually this isn’t a problem, but sometimes pivot tables or other features in Excel will read the space as a value, when it really should be read as 0.

The macro below will take whatever worksheet you’re on and will remove all the spaces from the otherwise aesthetically empty cells. It won’t remove spaces from cells that contain text, formulas, numbers, etc. After running this macro successfully, you really shouldn’t notice a visual difference in your spreadsheet. You’ll only notice a change if you know that a particular cell had a space prior to running the macro and notice that it doesn’t now.

Copy all of the code below. Paste it into your workbook’s Visual Basic editor, in a Module, or Microsoft Excel Object (This Workbook, Sheet1, Sheet2, etc.).

Need help? Use our nifty guide to help figure out how to install and use your macros.

Sub Remove_Spaces()

Dim rangeSheet As Range
Dim rangeText As Range

Set rangeText = Cells.SpecialCells( _
xlCellTypeConstants, _
For Each rangeSheet In rangeText
If Trim(rangeSheet.Value) = "" Then
End If
Set rangeText = Nothing
Set rangeSheet = Nothing
End Sub

  • Brandi Leath

    Thanks!! Very nice.

  • GW

    Fails if there are merged cells.

  • Bob Umlas

    Sub Remove_Spaces()
    For Each rg In cells.specialcells(xlCellTypeConstants, xlTextValues)
    If Trim(rg.Value) = “” Then rg.ClearContents
    End Sub