VBA批量删除单元格字符串空格问题求助：现有代码无法运行

Let's walk through fixing your code—you were right to reach for the Replace function, but a few syntax and logic missteps are keeping it from working. Here's what was off, plus a corrected version:

Issues in Your Original Code #

• Wrong Replace usage: Your line cell.Value = Replace(" ", "") doesn't reference the cell's actual content. The Replace function needs three arguments: the original string, what to replace, and what to replace it with.
• Unreliable last row detection: Using Find(" ") to get the last row will fail if the bottom cell in column B doesn't contain a space. We'll use a more robust method to find the last used row.
• Unclosed With block: Your code ends without End With, which causes a syntax error.
• Redundant check: The InStr check isn't necessary—Replace will leave the cell unchanged if there are no spaces, so we can skip that step entirely.

Corrected Code #

Sub RemoveAllSpaces()
    Dim cell As Range
    Dim LastRowSource As Long
    Dim ws1 As Worksheet
    
    Set ws1 = ActiveWorkbook.ActiveSheet
    
    ' Get the last used row in column B (much more reliable)
    LastRowSource = ws1.Cells(ws1.Rows.Count, "B").End(xlUp).Row
    
    ' Loop through each cell in B2 to the last row
    For Each cell In ws1.Range("B2:B" & LastRowSource)
        ' Replace all spaces in the cell with nothing
        cell.Value = Replace(cell.Value, " ", "")
    Next cell
End Sub

How This Works #

• We use ws1.Cells(ws1.Rows.Count, "B").End(xlUp).Row to find the last row with data in column B—this works regardless of whether the cell has spaces or not.
• The Replace(cell.Value, " ", "") line takes the cell's current content, removes every space, and writes the cleaned value back to the cell.
• No need for the InStr check because Replace handles empty cases gracefully: if there are no spaces, the cell's value stays the same.

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