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

 

[xdriver]

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)

#VALUE!

 

[Fluff]

Is there a way to post an actual excel file?

Not here, but you can uplaod to a site such as OneDrive, DropBox, GoogleDrive, then mark for sharing & post the link you are given to the thread.

 

[xdriver]

I believe there is something wrong with the sheet. Even trying to do a sum of cells is not working and yields "0." Also, selecting multiple cells does not show the Average and Sum at the bottom where it shows count even though sum and average are checked "on" in the settings. Creating a new sheet seems to work.

 

[awoohaw]

#VALUE!

are you certain the cell you were pointing to was truly not blank?

Have you done select all blank cells ( Highlight a range. Press F5, Click Special, Click Blanks, Click OK)?

 

[xdriver]

google drive link to file

 

[xdriver]

are you certain the cell you were pointing to was truly not blank?

Have you done select all blank cells ( Highlight a range. Press F5, Click Special, Click Blanks, Click OK)?

"No cells were found."

 

[Fluff]

Thanks for that, how about

Sub xdriver()
   With ActiveSheet.UsedRange
      .NumberFormat = "General"
      .Value = .Value
   End With
End Sub

 

[xdriver]

Thanks for that, how about

Sub xdriver()
   With ActiveSheet.UsedRange
      .NumberFormat = "General"
      .Value = .Value
   End With
End Sub

That DID work. However, when I tried doing select all on the sheet and formatting to general, it did not work. I also tried creating a new sheet and doing a copy paste of values only and that did not work either. What is the difference between the code you sent and when I manually tried it? Thank you very much for helping sort this out for me.

 

[Fluff]

When you copy/ paste as values you can get nullstrings in blank cells which is what you had. But when using .Value=.Value you get rid of the nullstrings.

 

[xdriver]

So it would almost be wise to run that VBA whenever you import a lot of data?