Empty Cells aren't empty

[timetabler]

I have web-site generated xls sheet (The site provides online data, I then press the excel icon and it creates and downloads as xls format). - The content appears to be raw (no colouring, or table lines)

The problem is that the cells which are empty are counted by excel i.e. select a2:a5 will count as 4 (bottom right of excel), as does counta(a2:a5).

To troubleshoot: code(a2) returns #VALUE! and ISBLANK(a2) returns FALSE, I have also tried paste special over the top, and also onto a new sheet. I have also searched for similar problems, but the code:

Sub test()
For Each Sheet In Sheets
Sheet.Cells.SpecialCells(xlCellTypeBlanks).ClearFormats
Next Sheet
End Sub

doesn't fix the problem
The only way I can solve the problem is to go into each cell (individually or in bulk) and press delete.

Is there a quicker way to solve the problem?

 

[VoG]

Try selecting the cells then run TrimAll Rearranging Data in Columns

 

[timetabler]

Thanks that worked!

 

[Gary's Student]

If ISBLANK() returns FALSE, then the cells contain null characters. To remove these "pseudo-blanks" try this small macro:

Sub ClearEmpties()
Dim rDel As Range, Cel As Range
Dim r As Range
Dim s As Worksheet
For Each s In Sheets
    s.Activate
    Set rDel = Nothing
    For Each Cel In ActiveSheet.UsedRange
        If Len(Cel.Value) = 0 And IsEmpty(Cel) = False Then
            If rDel Is Nothing Then
                Set rDel = Cel
            Else
                Set rDel = Union(rDel, Cel)
            End If
        End If
    Next
    If rDel Is Nothing Then
    Else
        rDel.Clear
    End If
Next
End Sub