VBA code to remove leading and trailing spaces from cells in table now failing

[BlondieC]

Hi, this code was working and now is failing and I'm not sure why. It was set to trim all leading and training spaces from all cells in MasterTbl. The line it fails on is in red in the below code. The following error message is returned: Run-time error '13': Type mismatch.

Sub RemoveAllSpaces()     'Macro Purpose: To trim all excess spaces out of cells.  This
     'eliminates issues where users have cleared the cell with a space,
     'and elimates all extra spaces at the beginning or end of a string
     
    Application.ScreenUpdating = False
     
    Dim cl As Variant
     
     'Loop through cells removing excess spaces
      For Each cl In ActiveSheet.Range("MasterTbl")
[COLOR=#ff0000][B]        If Len(cl) > Len(WorksheetFunction.Trim(cl)) Then[/B][/COLOR]
            cl.Value = WorksheetFunction.Trim(cl)
        End If
    Next cl
        
    Application.ScreenUpdating = True
    
End Sub

MasterTbl data from columns A:K with D as a hidden column:
This data at this point needs a lot of cleanup done but as step one I want leading and trailing spaces removed.

Old Box#	Civic#	Unit	Street Name	Street Type	Direction	Last Name	First Name	Convenience Box	Comments
1660						CENTRAL LAMBTON FAMILY HEALTH TEAM	269
1247						OPTIMIST CLUB		156
2001	MOVED		CHECK			ZAVITZ	DONALD
518	CHECK		ADDRESS/CONFIRM	UNIT		BROOKS	GERRY
843	408		ALBANY	ST		HARTLEY	LAWRENCE
1004	410		ALBANY	ST		621 TECHNOLOGIES IN.
929	413	C	ALBANY	ST		SHAKERS LOUNGE
1090	430		ALBANY	ST		HOBEN DENTISTRY
2094	436		ALBANY	ST		BUDS PIZZA	LORNE DOUGLAS
1451	437		ALBANY	ST		DOUGLAS	LORNE
13	432	1	ALBANY	ST.		SYKES	STEVE
310	413	A	ALBANY			OIL RIG RESTAURANT
1209	422		ALBANY			PETROLIA INSTANT PRINT	RANDY DRYDAK

<tbody>
</tbody>

Thank you!

 

[hiker95]

hiker95 this worked like a charm! Thank you so much! And the additional comments and conversation help me understand how it works. This is one more thing to help the end-users of this workbook I'm creating for them. Much appreciated.

BlondieC,

Thanks for the feedback.

You are very welcome. Glad I could help.

And, come back anytime.

 

[BlondieC]

I'm reading through other posts right now before I post my next question if I don't find something similar to what I'm trying to do now so I'll be here for a while.

BlondieC,

Thanks for the feedback.

You are very welcome. Glad I could help.

And, come back anytime.

 

[hiker95]

I'm reading through other posts right now before I post my next question if I don't find something similar to what I'm trying to do now so I'll be here for a while.

BlondieC,

I will be watching for your next question.