Getting Rid of Cells Containing Only Spaces

After my latest comment below, I realized there was no reason to clear a hyperlink from a null cell; it is only necessary to clear the text (making it blank). So the ClearNullCells macro in My Excel Toolbox has been updated accordingly.
The CleanSheet2 and CleanSheet3 macros in my earlier comment should also be changed. Locate the following statement in each
    If sTrim = "" Then rMerg.ClearContents
and replace it with this statement
    If sTrim = "" Then rMerg.Value = ""
Previously I wasn't aware the Range.ClearContents method also clears hyperlinks.

A null cell is one that contains only space characters (Unicode 32 or 160).
My Excel Toolbox now includes the ClearNullCells macro to clear all null cells (making them blank). Merged and/or hidden cells apply. Cells containing formulas are ignored. When a null cell is cleared, its format is not modified; any comment remains, but a hyperlink does not.
There are three options:
1. Clear null cells from all worksheets in all open workbooks
2. Clear null cells from all worksheets in the active workbook
3. Clear null cells from the active worksheet only
See https://sites.google.com/view/MyExcelToolbox/

@Craig Buback and Andy
Thanks for the debug assist. Here are updated versions of CleanSheet2 and CleanSheet3. Let me know if you find other issues.

Sub CleanSheet2()
    Dim rCell As Range, rText As Range, rMerg As Range, sTrim As String
    On Error Resume Next
    Set rText = Cells.SpecialCells(xlCellTypeConstants, xlTextValues)
    On Error GoTo 0
    If Not (rText Is Nothing) Then
        For Each rCell In rText
            Set rMerg = rCell.MergeArea
            If rCell.Address = rMerg.Cells(1).Address Then
                sTrim = Trim(Replace(rCell.Value, Chr(160), " "))
                If sTrim = "" Then rMerg.ClearContents
            End If
        Next rCell
    End If
End Sub

Sub CleanSheet3()
    Dim WB As Workbook, WS As Worksheet
    Dim rUsed As Range, rText As Range, rCell As Range, rMerg As Range
    Dim nWB As Long, nWS As Long, nC As Long, sTrim As String
    Dim nWBX As Long, nWSX As Long, nWBC As Long, nWSC As Long
    For Each WB In Workbooks
        nWB = nWB + 1: nWBX = nC
        For Each WS In WB.Worksheets
            nWS = nWS + 1: nWSX = nC
            Set rUsed = WS.UsedRange
            On Error Resume Next
            Set rText = rUsed.SpecialCells(xlCellTypeConstants, xlTextValues)
            On Error GoTo 0
            If Not (rText Is Nothing) Then
                For Each rCell In rText
                    Set rMerg = rCell.MergeArea
                    If rCell.Address = rMerg.Cells(1).Address Then
                        sTrim = Trim(Replace(rCell.Value, Chr(160), " "))
                        If sTrim = "" Then rMerg.ClearContents: nC = nC + 1
                    End If
                Next rCell
                Set rText = Nothing
            End If
            If nWSX < nC Then nWSC = nWSC + 1
        Next WS
        If nWBX < nC Then nWBC = nWBC + 1
    Next WB
    MsgBox nC & " null cells containing only space characters" & vbNewLine _
        & "have been cleared from " & nWSC & " of " & nWS & " worksheets" _
        & vbNewLine & "in " & nWBC & " of " & nWB & " open workbooks."
End Sub

What about the dreaded character code 160 spaces? If any of the contents of the worksheet were sourced from the web, they may not be regular spaces. I have been caught out by this before.

The CleanSheet macro returns an error on my sheet which contained merged cells

The CleanSheet2 macro in my previous comment below avoids the error discussed there. But Hanna "has a bunch of workbooks" with "various worksheets" to clean. This version will check all worksheets in all open workbooks and report the number of cells modified. It also treats non-break space Chr(160) the same as standard space.

Sub CleanSheet3()
    Dim WB As Workbook, WS As Worksheet
    Dim rUsed As Range, rText As Range, rCell As Range, nbr As Long
    For Each WB In Workbooks
        For Each WS In WB.Worksheets
            Set rUsed = WS.UsedRange
            On Error Resume Next
            Set rText = rUsed.SpecialCells(xlCellTypeConstants, xlTextValues)
            On Error GoTo 0
            If Not (rText Is Nothing) Then
                For Each rCell In rText
                    If Trim(Replace(rCell.Value, Chr(160), " ")) = "" Then
                        rCell.ClearContents
                        nbr = nbr + 1
                    End If
                Next rCell
                Set rText = Nothing
            End If
        Next WS
    Next WB
    MsgBox "In all worksheets of all open workbooks, " & nbr _
        & " null cells containing only space characters have been cleared."
End Sub

For more about Range.SpecialCells, see my recent comment here: https://excelribbon.tips.net/T012552_Skipping_Hidden_Rows_in_a_Macro.html

The Tip's macro has an issue. If the active worksheet has no text constants, the macro's result will be an error message. Maybe that is an acceptable result. (see Figure 1 below)
Perhaps nobody will run the macro unless the worksheet has text, but that text might be the result of formulas instead of constants. In this case, the result will be the same error message.
Here is a modified version of the Tip's macro that avoids the error:

Sub CleanSheet2()
    Dim rCell As Range, rText As Range
    On Error Resume Next
    Set rText = Cells.SpecialCells(xlCellTypeConstants, xlTextValues)
    On Error GoTo 0
    If Not (rText Is Nothing) Then
        For Each rCell In rText
            If Trim(rCell.Value) = "" Then rCell.ClearContents
        Next rCell
    End If
End Sub