CODEWITHSUNDEEP
excelvba
Akash Panchal
Akash Panchal

Reputation: 71

Remove Spaces From Entire Column VBA

I'd like to remove spaces from all the text values within column B, starting at row B2. Here is a piece of code that works by removing the spaces, line by line, through column B:

Sub RemoveSpacing()

    Set AssetToolSheet = ActiveWorkbook.Worksheets("AssetName Sheet")
    Dim startingLine As Double
    startingLine = 2
    
    With AssetToolSheet
        Do While .Cells(startingLine, 2) <> ""
            .Cells(startingLine3, 2).Replace " ", ""
            startingLine = startingLine + 1
        Loop
    End With

End Sub

Would like to reference an entire range (for example, B2:B) and remove spaces all at once, rather than line by line.

Thank you!

Upvotes: 0

Views: 722

Answers (1)

Sathish Kothandam
Sathish Kothandam

Reputation: 1520

you could start below code which will replaces all spaces in A column without looping through each cell.

Sub Macro1()
' replace spaces with ''
    Columns("A:A").Replace What:=" ", Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows
End Sub

Upvotes: 1

Related Questions