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.

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


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.