Excel Forum

Code: Select all

  Sub Delete_Sp()
  Dim i As Long
  Dim j As Long
  Dim a As String
  i = 1   'start on first row
  Application.ScreenUpdating = False
  Do While i <= ActiveSheet.UsedRange.Rows.Count

    For j = i + 1 To ActiveSheet.UsedRange.Rows.Count
        
        'Cells(j, 4) = "=SUBSTITUTE(a1,"" "","""")"
        a = Cells(j, 4)
        a.Select
        SUBSTITUTE(a," ","")
    Exit For

    Next j
    i = i + 1
  Loop
  Application.ScreenUpdating = True
  
  End Sub

Code: Select all

Sub test()
    For Each r In ActiveSheet.UsedRange
        If r.Row > 1 Then r.Value = Replace(r, " ", "")
    Next r
End Sub

Code: Select all

Sub Delete_SP()
    'For Each r In ActiveSheet.UsedRange
    X = Workbooks("final.xlsm").Worksheets("Sheet7").Columns("B")
    X.UsedRange
    For Each r In X
     If r.Row > 1 Then r.Value = Replace(r, " ", "")
    Next r
End Sub

Code: Select all

Sub Delete_SP()
    Dim rAll As Range, r As Range
    With Sheets("Sheet7")
        Set rAll = .Range("B2", _
            .Range("B" & Rows.Count).End(xlUp))
    End With
    For Each r In rAll
        r.Value = Replace(r, " ", "")
    Next r
End Sub

Code: Select all

Sub Delete_SP()
    Dim rAll As Range, r As Range
    With Sheets("Sheet14")
        Set rAll = .Range("k1", _
            .Range("k" & Rows.Count).End(xlUp))
            rAll.Select
    End With
    For Each r In rAll
        r.Select
 '       r.Value = Replace(r, " ", "")
        r.Value = Replace(r, "", "0")
 '       r.Value = Replace(r, "N.A.", "")
    Next r
    Call Sound_2
End Sub

Code: Select all

Sub Delete_SP()
    Dim rAll As Range, r As Range
    With Sheets("Sheet7")
        Set rAll = .Range("B2", _
            .Range("B" & Rows.Count).End(xlUp))
    End With
    For Each r In rAll
        r.Value = Replace(r, " ", "")
    Next r
End Sub

Code: Select all

Sub Delete_SP()
    Dim sh As Worksheet
    Dim rAll As Range, r As Range
    For Each sh In Worksheets
        With sh
            Set rAll = .Range("B2", _
                .Range("B" & Rows.Count).End(xlUp)).Resize(, 20)
        End With
        For Each r In rAll
            r.Value = Replace(r, " ", "")
        Next r
    Next sh
End Sub