Possible hidden non-printable character removal

[xdriver]

Here is a snippit of one of my sheets. If I select a column it counts every single column, not just the ones with data in them. If I select a bunch of "empty" cells, and click delete, then the count will be reduced by the number of cells I selected. I tried copying "empty" cells, and pasting it into View non-printable unicode characters but receive nothing because there is nothing in the cells to select. How can I clean up my sheet of non printable characters?

natickmlspublicrecords.xlsx
	X	Y	Z	AA	AB	AC	AD
1	Creation Date	Exterior	Fireplaces	First Floor Sqft	First Name	Foundation	Fuel Type
2
3
4					CHRISTOPHER
5		ALVNL	1	1336	MICHAEL		OIL
6
7
8					ELLEN G
9
10
11
12		ALVNL	1	768	JOHN W		ELEC
Sheet1

 

[Fluff]

Do you have any formulae on that sheet?

 

[awoohaw]

you could try this macro.
160 is the Code for one of the zero length non printing characters.
If there are others, just add a loop to loop through the different characters that are causing errors.

You'll have to amend it to fit your range of cells you seek.

Sub RemoveCharacter()
'
' RemoveCharacter Macro
'
Dim x As Variant
'
    Range("A1:P22").Select
       x = Chr(160)
 
    Selection.Replace What:=x, Replacement:=null, LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
End Sub

to search for blank cells:

after macro is run:

The pink cells are conditional formats looking for the char(160) value.

 

[xdriver]

Do you have any formulae on that sheet?

No formulas in this sheet, it is more of a "database" scraped from a webpage.

 

[Fluff]

Ok, try

Sub xdriver()
   With ActiveSheet.UsedRange
      .Value = .Value
   End With
End Sub

 

[xdriver]

Ok, try

Sub xdriver()
   With ActiveSheet.UsedRange
      .Value = .Value
   End With
End Sub

This didn't work either. Is there a way to post an actual excel file?

 

[xdriver]

you could try this macro.
160 is the Code for one of the zero length non printing characters.
If there are others, just add a loop to loop through the different characters that are causing errors.

You'll have to amend it to fit your range of cells you seek.

Sub RemoveCharacter()
'
' RemoveCharacter Macro
'
Dim x As Variant
'
    Range("A1:P22").Select
       x = Chr(160)
 
    Selection.Replace What:=x, Replacement:=null, LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
End Sub

to search for blank cells:

View attachment 107961

after macro is run:

View attachment 107963


The pink cells are conditional formats looking for the char(160) value.







View attachment 107962

This didn't seem to work either as it found none.

 

[awoohaw]

do you know the character code that is causing problems?
You need to change the 160 in the vba to that number.

 

[xdriver]

do you know the character code that is causing problems?
You need to change the 160 in the vba to that number.

I don't because I can't select anything in the cell, there is nothing in them at all.

 

[awoohaw]

in a cell that is really blank near a cell that has the zero length character, put this formula:
Assume it is cell C3, so put this in cell D3:

=CODE(C3)