Removing space and ChrW from cells in VBA

Question

This code is working when a paste is done it will force the formatting to be the same as in the destination sheet and will remove space and ChrW from the cells as soon as the paste event is detected. Is there any way to improve the code to work better?

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim cell As Range
On Error Resume Next
 With Application.CommandBars("Standard").Controls("&Undo")
 If .ListCount < 1 Then Exit Sub '<--- this line creates an error if on error resume next is commented when reset filter is pressed
 If .List(1) <> "Paste" Then Exit Sub
 End With
 Application.ScreenUpdating = False
 Application.EnableEvents = False
 Selection.Replace ChrW(160), " ", xlPart
 For Each cell In Selection
 cell.Value2 = WorksheetFunction.Trim(WorksheetFunction.Clean(cell.Value2))
 Next
 Application.CutCopyMode = False
 Application.EnableEvents = True
 Application.ScreenUpdating = True
End Sub

And this is the code for the reset filter:

Sub resetFilters()
Dim sht As Worksheet
Dim LastRow As Range
Application.ScreenUpdating = False
 On Error Resume Next
 If ActiveSheet.FilterMode Then
 ActiveSheet.ShowAllData
 End If
Range("A3:T3").ClearContents
Application.ScreenUpdating = True
Call GetLastRow
End Sub

Answer 1

On Error Resume Next is not some kind of magic that makes bugs go away. It simply hides them and makes your code completely unpredictable.

Let me repeat that, because it's of the utmost importance....

On Error Resume Next makes your code completely unreliable, unpredictable, and unsafe. Once an error occurs, your program is left in an unknown and invalid state, but continues to chug along pretending everything is fine. In the meantime, totally wrecking all your data.

So, remove that evil line of code, take the time to recreate the error, figure out why it's happening, then fix it. Once you've done that, come back and we'd be happy to help you turn your "good enough" code into Good CodeTM.

Answer 2

I'm on my cell so I apologize for any mistakes.

The first IF statement has an error because you have initiated a with statement that is immediately evaluated on a count of that object. Instead of if .listcount<1 use a variable for the .listcount of the object:

Count = object.listcount

Then use the WITH OBJECT statement. Then use:

IF count < 1 then... :end 
with: exit sub

• QuickSilver: Tried and if I lose the on error resume next it's giving me errors. Tried different approaches and still didn't work still researching online to see if I can find a solution for the problem.
• Allen Mattson: Above the 'dim cell as range', I put 'set target = selection'. I also removed the on error resume next portion. This worked to preserve the formatting on the selection of the pasted area. If you are considering a new approach, you may want to move this to a vbaproject module (instead of a workbook module) because you have nothing distinguishing a workbook event just the users actions for paste. I think it would make your project easier for you to troubleshoot and build off of.