Macro to remove spaces in existing excel columns

Help Activities
, ,
1

Suggest a macro to delete spaces in column L,P,Q,R,U,AR,As,AT,AU,AV so that this column would be free from the unwanted spaces

2

@Gopi_Krishna1

Sub RemoveSpaces()
    Dim ws As Worksheet
    Dim columnsArray As Variant
    Dim col As Variant
    Dim lastRow As Long

    ' Set the worksheet
    Set ws = ThisWorkbook.Sheets("YourSheetName") ' Replace "YourSheetName" with the actual sheet name
    
    ' Specify columns to remove spaces
    columnsArray = Array("L", "P", "Q", "R", "U", "AR", "AS", "AT", "AU", "AV")
    
    ' Loop through each specified column
    For Each col In columnsArray
        ' Find the last used row in the column
        lastRow = ws.Cells(ws.Rows.Count, col).End(xlUp).Row
        
        ' Remove spaces in the entire column
        ws.Range(col & "1:" & col & lastRow).Replace " ", ""
    Next col
End Sub

image
image527×666 24.4 KB

3

Hi @Gopi_Krishna1

If possible share us the input excel.
Try this:

Sub RemoveSpacesFromColumns()
    Dim ws As Worksheet
    Dim columnRange As Range
    Dim col As Variant
    
    ' Specify the columns to remove spaces from
    Dim columnsToClean As Variant
    columnsToClean = Array("A", "G", "L", "P", "Q", "R", "U", "AR", "AS", "AT", "AU", "AV")
    
    ' Reference the active worksheet
    Set ws = ActiveSheet
    
    ' Loop through each column and remove spaces
    For Each col In columnsToClean
        Set columnRange = ws.Columns(col)
        columnRange.Replace What:=" ", Replacement:="", LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False
    Next col
End Sub

Hope it helps!!
Regards

4

It’s working good thank you

5

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.