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!

 

[BlondieC]

I just tried running the code in smaller chunks and it looks like it was failing when it encountered this:

#NAME?

<tbody>
</tbody>

in one of the cells. Nothing worse than working with really dirty data.

 

[hiker95]

BlondieC,

1. What version of Excel, and, Windows are you using?

2. Are you using a PC or a Mac?


Here is a macro solution for you to consider, that does not loop thru all the cells in the active worksheet that contains the MasterTbl.

Sample raw data with the column D not hidden for display purposes:

Excel 2007
	A	B	C	D	E	F	G	H	I	J
1	Old Box#	Civic#	Unit	Street Name	Street Type	Direction	Last Name	First Name	Convenience Box	Comments
2	1660						CENTRAL LAMBTON FAMILY HEALTH TEAM		269
3	1247						OPTIMIST CLUB		156
4	2001	MOVED		CHECK			ZAVITZ	DONALD
5	518	CHECK		ADDRESS/CONFIRM	UNIT		BROOKS	GERRY
6	843	408		ALBANY	ST		HARTLEY	LAWRENCE
7	1004	410		ALBANY	ST		621 TECHNOLOGIES IN.
8	929	413	C	ALBANY	ST		SHAKERS LOUNGE
9	1090	430		ALBANY	ST		HOBEN DENTISTRY
10	2094	436		ALBANY	ST		BUDS PIZZA	LORNE DOUGLAS
11	1451	437		ALBANY	ST		DOUGLAS	LORNE
12	13	432	1	ALBANY	ST.		SYKES	STEVE
13	310	413	A	ALBANY			OIL RIG RESTAURANT
14	1209	422		ALBANY			PETROLIA INSTANT PRINT	RANDY DRYDAK
15
Sheet1

And, if we hide column D before running the macro:

Excel 2007
	A	B	C	E	F	G	H	I	J
1	Old Box#	Civic#	Unit	Street Type	Direction	Last Name	First Name	Convenience Box	Comments
2	1660					CENTRAL LAMBTON FAMILY HEALTH TEAM		269
3	1247					OPTIMIST CLUB		156
4	2001	MOVED				ZAVITZ	DONALD
5	518	CHECK		UNIT		BROOKS	GERRY
6	843	408		ST		HARTLEY	LAWRENCE
7	1004	410		ST		621 TECHNOLOGIES IN.
8	929	413	C	ST		SHAKERS LOUNGE
9	1090	430		ST		HOBEN DENTISTRY
10	2094	436		ST		BUDS PIZZA	LORNE DOUGLAS
11	1451	437		ST		DOUGLAS	LORNE
12	13	432	1	ST.		SYKES	STEVE
13	310	413	A			OIL RIG RESTAURANT
14	1209	422				PETROLIA INSTANT PRINT	RANDY DRYDAK
15
Sheet1

Then we run the macro, and, then unhide column D, and, we get this:

Excel 2007
	A	B	C	D	E	F	G	H	I	J
1	Old Box#	Civic#	Unit	Street Name	Street Type	Direction	Last Name	First Name	Convenience Box	Comments
2	1660						CENTRAL LAMBTON FAMILY HEALTH TEAM		269
3	1247						OPTIMIST CLUB		156
4	2001	MOVED		CHECK			ZAVITZ	DONALD
5	518	CHECK		ADDRESS/CONFIRM	UNIT		BROOKS	GERRY
6	843	408		ALBANY	ST		HARTLEY	LAWRENCE
7	1004	410		ALBANY	ST		621 TECHNOLOGIES IN.
8	929	413	C	ALBANY	ST		SHAKERS LOUNGE
9	1090	430		ALBANY	ST		HOBEN DENTISTRY
10	2094	436		ALBANY	ST		BUDS PIZZA	LORNE DOUGLAS
11	1451	437		ALBANY	ST		DOUGLAS	LORNE
12	13	432	1	ALBANY	ST.		SYKES	STEVE
13	310	413	A	ALBANY			OIL RIG RESTAURANT
14	1209	422		ALBANY			PETROLIA INSTANT PRINT	RANDY DRYDAK
15
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Sub RemoveLeadingTrailingSpaces()
' hiker95, 02/26/2016, ME9242394
With ActiveSheet
  With .Range("MasterTbl")
    .Value = Evaluate("IF(ISTEXT(" & .Address & "),TRIM(" & .Address & "),REPT(" & .Address & ",1))")
  End With
End With
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm, and, answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

Then run the RemoveLeadingTrailingSpaces macro.


The word wrapping in the YELLOW cells is only happening in the MrExcel display area, and, not in the worksheet.

 

[Rick Rothstein]

Sub RemoveLeadingTrailingSpaces()
' hiker95, 02/26/2016, ME9242394
With ActiveSheet
  With .Range("MasterTbl")
    .Value = Evaluate("IF(ISTEXT(" & .Address & "),TRIM(" & .Address & "),REPT(" & .Address & ",1))")
  End With
End With
End Sub

I like that method of controlling the "0 if the cell is empty" problem.

 

[hiker95]

I like that method of controlling the "0 if the cell is empty" problem.

Rick Rothstein,

Thank you very much.

I usually keep track of where I find gems like this, but, in this case, I did not.

I am going to say that the code was probably one of your gems that is in my archives.

Thanks again.

 

[Rick Rothstein]

Rick Rothstein,

Thank you very much.

I usually keep track of where I find gems like this, but, in this case, I did not.

I am going to say that the code was probably one of your gems that is in my archives.

The structure of the Evaluate function looks to be mine, but the use of REPT like that is not. My "go to" method to handle the problem was (before I saw your treatment, that is :wink this...

[table="width: 500"]
[tr]
	[td].Value = Evaluate("IF(ISTEXT(" & .Address & "),TRIM(" & .Address & "),[COLOR="#FF0000"]IF(" & .Address & "="""",""""," & .Address & ")[/COLOR])")[/td]
[/tr]
[/table]

 

[hiker95]

Rick Rothstein,

I just checked your new code line, in my macro with the displayed raw data, and, it returned the same results.

Thanks again for another gem for my archives.

 

[BlondieC]

Hi and thank you to everyone for stopping in to help. Today is my first day to look at the suggestions and try them out. I'm working on this for another department so I fit it in when I can and month-end reports consumed most of my time before getting back to this.

I am using Excel 2013 and Windows 7 Enterprise.

I do have this workbook saved as macro enabled and have a test version to work in. Going to try out the suggestions now.

BlondieC,

1. What version of Excel, and, Windows are you using?

2. Are you using a PC or a Mac?


Here is a macro solution for you to consider, that does not loop thru all the cells in the active worksheet that contains the MasterTbl.

Sample raw data with the column D not hidden for display purposes:

Excel 2007

	A	B	C	D	E	F	G	H	I	J
1	Old Box#	Civic#	Unit	Street Name	Street Type	Direction	Last Name	First Name	Convenience Box	Comments
2	1660						CENTRAL LAMBTON FAMILY HEALTH TEAM		269
3	1247						OPTIMIST CLUB		156
4	2001	MOVED		CHECK			ZAVITZ	DONALD
5	518	CHECK		ADDRESS/CONFIRM	UNIT		BROOKS	GERRY
6	843	408		ALBANY	ST		HARTLEY	LAWRENCE
7	1004	410		ALBANY	ST		621 TECHNOLOGIES IN.
8	929	413	C	ALBANY	ST		SHAKERS LOUNGE
9	1090	430		ALBANY	ST		HOBEN DENTISTRY
10	2094	436		ALBANY	ST		BUDS PIZZA	LORNE DOUGLAS
11	1451	437		ALBANY	ST		DOUGLAS	LORNE
12	13	432	1	ALBANY	ST.		SYKES	STEVE
13	310	413	A	ALBANY			OIL RIG RESTAURANT
14	1209	422		ALBANY			PETROLIA INSTANT PRINT	RANDY DRYDAK
15

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

And, if we hide column D before running the macro:

Excel 2007

	A	B	C	E	F	G	H	I	J
1	Old Box#	Civic#	Unit	Street Type	Direction	Last Name	First Name	Convenience Box	Comments
2	1660					CENTRAL LAMBTON FAMILY HEALTH TEAM		269
3	1247					OPTIMIST CLUB		156
4	2001	MOVED				ZAVITZ	DONALD
5	518	CHECK		UNIT		BROOKS	GERRY
6	843	408		ST		HARTLEY	LAWRENCE
7	1004	410		ST		621 TECHNOLOGIES IN.
8	929	413	C	ST		SHAKERS LOUNGE
9	1090	430		ST		HOBEN DENTISTRY
10	2094	436		ST		BUDS PIZZA	LORNE DOUGLAS
11	1451	437		ST		DOUGLAS	LORNE
12	13	432	1	ST.		SYKES	STEVE
13	310	413	A			OIL RIG RESTAURANT
14	1209	422				PETROLIA INSTANT PRINT	RANDY DRYDAK
15

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Then we run the macro, and, then unhide column D, and, we get this:

Excel 2007

	A	B	C	D	E	F	G	H	I	J
1	Old Box#	Civic#	Unit	Street Name	Street Type	Direction	Last Name	First Name	Convenience Box	Comments
2	1660						CENTRAL LAMBTON FAMILY HEALTH TEAM		269
3	1247						OPTIMIST CLUB		156
4	2001	MOVED		CHECK			ZAVITZ	DONALD
5	518	CHECK		ADDRESS/CONFIRM	UNIT		BROOKS	GERRY
6	843	408		ALBANY	ST		HARTLEY	LAWRENCE
7	1004	410		ALBANY	ST		621 TECHNOLOGIES IN.
8	929	413	C	ALBANY	ST		SHAKERS LOUNGE
9	1090	430		ALBANY	ST		HOBEN DENTISTRY
10	2094	436		ALBANY	ST		BUDS PIZZA	LORNE DOUGLAS
11	1451	437		ALBANY	ST		DOUGLAS	LORNE
12	13	432	1	ALBANY	ST.		SYKES	STEVE
13	310	413	A	ALBANY			OIL RIG RESTAURANT
14	1209	422		ALBANY			PETROLIA INSTANT PRINT	RANDY DRYDAK
15

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Sub RemoveLeadingTrailingSpaces()
' hiker95, 02/26/2016, ME9242394
With ActiveSheet
  With .Range("MasterTbl")
    .Value = Evaluate("IF(ISTEXT(" & .Address & "),TRIM(" & .Address & "),REPT(" & .Address & ",1))")
  End With
End With
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm, and, answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

Then run the RemoveLeadingTrailingSpaces macro.


The word wrapping in the YELLOW cells is only happening in the MrExcel display area, and, not in the worksheet.

 

[BlondieC]

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]

Is there somewhere I should indicate this has been resolved?