# Removing space and ChrW from cells in VBA

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


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 Code™.

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

• 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. Apr 2 '17 at 16:40
• 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. Apr 3 '17 at 17:26