Clean Trim Functions does not work accurately on Filtered Cell-CodePudding

I have been using this function to clean and trim the selected data from the sheet but when i Filtered the range and apply this function on filtered cell it copies and paste the above value till down.

Any help will be appreciated.

Before applying the function:

After applying the function:

Sub CleanTrim()
  Application.ScreenUpdating = False
With Selection.SpecialCells(xlCellTypeVisible)
.NumberFormat = "@"
.Value = Application.Clean(Application.Trim(.Value))
  Application.ScreenUpdating = True
End With
End Sub

CodePudding user response：

You will need to loop through the Areas in Selection.SpecialCells(xlCellTypeVisible) and apply the trimming to each area in turn - it will not work on a discontinuous range like the one you typically get from SpecialCells

Sub CleanTrim()
    Dim a As Range
    Application.ScreenUpdating = False
    For Each a In Selection.SpecialCells(xlCellTypeVisible).Areas
        a.NumberFormat = "@"
        a.Value = Application.Clean(Application.Trim(a.Value))
    Next a
    Application.ScreenUpdating = True
End Sub