VBA代码运行无报错但无法删除工作表所有空格，求解决

原代码的问题点 #

• 单元格引用顺序错误：ws1.Cells(c, r) 写法颠倒了行和列的参数，VBA中Cells的正确格式是Cells(行号, 列号)，需改为ws1.Cells(r, c)。
• 未将修改结果写回单元格：代码仅遍历字符把空格替换为空，但没有将处理后的字符串重新赋值给单元格，等于没有实际修改内容。
• 逐字符遍历效率低下：无需逐个字符检查，VBA内置的Replace函数可一次性替换所有空格，效率更高。

修正后的高效版代码 #

Sub removeSpaces(ws1 As Worksheet)
    Dim r As Long, c As Integer
    Dim cellContent As String
    Application.ScreenUpdating = False
    Application.StatusBar = "Deleting Spaces..."
    
    With ws1.UsedRange
        For c = 1 To .Columns.Count
            For r = 1 To .Rows.Count
                cellContent = .Cells(r, c).Value
                ' 替换所有空格
                cellContent = Replace(cellContent, " ", "")
                ' 将处理后的内容写回单元格
                .Cells(r, c).Value = cellContent
            Next r
        Next c
    End With
    
    Application.StatusBar = False
    Application.ScreenUpdating = True
End Sub

Sub callRemoveSpaces()
    removeSpaces ActiveWorkbook.Worksheets("Sheet1")
End Sub

保留逐字符逻辑的版本（方便后续扩展处理换行符） #

如果后续需要处理换行符等更多特殊字符，可修改逐字符逻辑，重新构建字符串后写回：

Sub removeSpaces(ws1 As Worksheet)
    Dim r As Long, c As Integer, s As Integer
    Dim cellContent As String, newContent As String
    Dim myChar As String
    Application.ScreenUpdating = False
    Application.StatusBar = "Deleting Spaces..."
    
    With ws1.UsedRange
        For c = 1 To .Columns.Count
            For r = 1 To .Rows.Count
                cellContent = .Cells(r, c).Value
                newContent = ""
                For s = 1 To Len(cellContent)
                    myChar = Mid(cellContent, s, 1)
                    ' 跳过空格，后续可添加跳过换行符的判断
                    If myChar <> " " Then
                        newContent = newContent & myChar
                    End If
                Next s
                ' 写回处理后的内容
                .Cells(r, c).Value = newContent
            Next r
        Next c
    End With
    
    Application.StatusBar = False
    Application.ScreenUpdating = True
End Sub

后续处理换行符的扩展 #

若要同时删除换行符，可在高效版代码中添加以下替换逻辑：

' 替换普通换行（vbLf）和回车换行（vbCrLf）
cellContent = Replace(cellContent, vbLf, "")
cellContent = Replace(cellContent, vbCrLf, "")

内容的提问来源于stack exchange，提问作者Max Pizzimenti