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()
'MACROS BY EXCELZOOM.COM

Dim rangeSheet As Range
Dim rangeText As Range

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

Comments

  1. Bob Umlas says

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

Leave a Reply

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

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>