Runtime error 1004, No cells found. Since there is a empty sheet (a sheet with nothing)
Below is just sample code.
Sub removeextraspace()
Dim WS As Worksheet
Dim WB As Workbook
Dim aCell As Range
Application.ScreenUpdating = False
ThisWorkbook.Sheets(1).Cells(20, 2).Value = " Hi Hi "
ThisWorkbook.Sheets(2).Cells(20, 3).Value = " XYZ XYZ "
For Each WS In ThisWorkbook.Worksheets
For Each aCell In WS.UsedRange.SpecialCells(xlCellTypeConstants)
aCell = WorksheetFunction.Trim(aCell)
Next aCell
Next
Application.ScreenUpdating = True
End Sub
Bookmarks