Thread: Clean the UsedRange

What does your CleanString function look like?

Here is how I would do the above code:

Sub Cells_CleanUsedRange()
' "Cleans" contents of the used range on the active worksheet
    
    Dim cll As Range
    
    With Application
        .ScreenUpdating = False
        With ActiveSheet
            For Each cll In .UsedRange
                If Not cll.HasFormula Then
                    cll.Value = CleanString(cll.Value)
                End If
            Next cll
            .UsedRange.Columns.AutoFit
            .UsedRange.Rows.AutoFit
        End With
        .ScreenUpdating = True
    End With
End Sub

hi jomili, two questions:
1. Why do you use Select in your code?
2. The flickering comes from looping the cells I guess. You could possibly try to use Specialcells (data volume is unknown)

You could also try this:

Sub Cells_CleanUsedRange()
' "Cleans" contents of the used range on the active worksheet
    
    Dim UsdRngValues As Variant
    Dim UsdRngFormulas As Variant
    Dim x As Long
    Dim y As Long
    
    With Application
        .ScreenUpdating = False
        With ActiveSheet
            UsdRngValues = .UsedRange.Value
            UsdRngFormulas = .UsedRange.Formula
            For x = 1 To UBound(UsdRngValues, 2)
                For y = 1 To UBound(UsdRngValues, 1)
                    If UsdRngValues(y, x) = UsdRngFormulas(y, x) Then
                        .Cells(y, x) = CleanString(UsdRngValues(y, x))
                    End If
                Next y
            Next x
            .UsedRange.Columns.AutoFit
            .UsedRange.Rows.AutoFit
        End With
        .ScreenUpdating = True
    End With
End Sub

Roy,
I was working on the same lines as you, prompted by Watersev's suggestion. Here's my crude attempt:

Dim Cell As Range
    With ActiveSheet
        
               
            For Each Cell In .UsedRange.SpecialCells(xlCellTypeVisible)
                If Not Cell.HasFormula Then
                    Cell.Value = CleanString(Cell.Value)
                End If
            Next Cell
                       .UsedRange.Columns.AutoFit
            .UsedRange.Rows.AutoFit
        End With
   
End Sub

Yours looks a lot cleaner. Why did you use xlCellTypeConstants? I would have thought Visible would work...never mind, it just clicked! Using constants I don't have to have my check for HasFormula. Duh! You make it look so easy!

Thanks to everyone for all the help on this one. I think you've all improved my coding loads. I appreciate all your efforts.

Thanks, and Happy New Year,
John

Roy,

I just noticed your code uses "ClearContents", not "CleanString". How do we adapt it? I tried

.SpecialCells(xlCellTypeConstants).value = CleanString(SpecialCells(xlCellTypeConstants).Value)

but that resulted in an error.

Try:

Sub CleanUsedRange()
' "Cleans" contents of the used range on the active worksheet

    Dim Cll As Range
    On Error GoTo exit_proc
        With ActiveSheet.UsedRange
            For Each Cll In .SpecialCells(xlCellTypeConstants)
                Cll.Value = CleanString(Cll.Value)
            Next Cll
            .Columns.AutoFit
            .Rows.AutoFit
        End With
exit_proc:
        MsgBox "No cells to clean"
End Sub

Thanks Whizbang; that does the trick! Thanks so much for the followthrough.

Happy New Year!