Delete all spaces in a worksheet

Viewed 27

I am writing a function to remove all spaces (and later new lines) in a spreadsheet for use in a comparison tool. Admittedly, it has been awhile since I have been back to VBA, but I finally have code that runs without errors, but it doesn't seem to do anything. All I am trying to do is loop through all the characters in a string, and if it is a space, turn it into nothing.

My code:

Sub removeSpaces(ws1 As Worksheet)
    Dim r As Long, c As Integer, s As Integer, cellContent As String
    Dim myChar As Variant
    
Dim lr1 As Long, lr2 As Long, lc1 As Integer, lc2 As Integer
Dim maxR As Long, maxC As Integer, cf1 As String, cf2 As String
Dim rptWB As Workbook, DiffCount As Long, SameCount As Long, TotalCount As Long
    Application.ScreenUpdating = False
    Application.StatusBar = "Deleting Spaces..."
    With ws1.UsedRange
        lr1 = .Rows.Count
        lc1 = .Columns.Count
    End With
    maxR = lr1
    maxC = lc1
    For c = 1 To maxC
        For r = 1 To maxR
            cellContent = ws1.Cells(c, r)
            For s = 1 To Len(cellContent)
                myChar = Mid(cellContent, s, 1)
                If myChar = " " Then
                    myChar = ""
                End If
            Next
        Next
    Next
    Application.StatusBar = False
    Application.ScreenUpdating = True


End Sub

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

End Sub

I would really appreciate any insights into whether my code is actually doing what I think it is. Thank you.

1 Answers

There's no need to loop. Use Range.Replace:

ws1.UsedRange.Replace What:=" ", Replacement:="", LookAt:=xlPart
Related