VBA macro to count & highlight non-unicode cells

[Dimitris254]

here's my problem:

i work with an excel file that has cells primarily in English, however some of them are in Chinese or other non-Unicode (or whatever is the default Excel encoding) characters.

I would like a macro to change the font color of the cells with such characters, and maybe in the end also mentioning the total number of those cells.

 

[Worf]

Here is an idea:

Sub Dimitris()
Dim cell As Range, weird As Boolean, cnt&
cnt = 0
For Each cell In ActiveSheet.UsedRange
    weird = False
    If Not IsError(cell) Then
        If Len(cell) > 0 Then
            Select Case Evaluate("=trim(unicode(" & cell.Address & "))")
                Case 20000 To 40000                     ' undesired
                    weird = True
                Case 7500 To 10500                      ' undesired
                    weird = True
            End Select
            If weird Then
                cell.Interior.Color = RGB(250, 250, 5)
                cnt = cnt + 1
            End If
        End If
    End If
Next
MsgBox cnt & " cells were highlighted."
End Sub

 

[Worf]

Did you finish that charting project from last month?

 

[Dimitris254]

Hey Worf, thank you for the macro; i get "type mismatch 13" error when right after the Select Case statement
I don't know if this macro helps, i couldn't get it to work either.

However, your idea made me wonder if searching for non-unicode chars is the way to go. In the end, i want to generate a CSV (comma delimited) file from the xlsx without question marks in place of chars that are not correctly encoded. I have tried add-ins and manual ways of keeping this characters in a UTF-8 CSV, but the database will not accept the imported file. So, i'm now opting back to generating an ASCII CSV file, but before doing that i would like to eliminate the chars that cannot be encoded correctly - but in a file of 4k rows, i'd rather have them highlighted. For example the i would edit the ü to ue or just u.


PS: Yes, that other project is finally done, the little data table squares were deemed unnecessary in the end, hence i didn't continue

 

[Worf]

The Unicode function I am using was introduced with Excel 2013, what is your version?

 

[Dimitris254]

The Unicode function I am using was introduced with Excel 2013, what is your version?

my bad - i'm using 2010 atm

also, as an added request, is it also possible to highlight cells with new line (i think it's CHAR( 10)) ? Afaik CSV ASCII files don't like new lines either.

 

[Worf]

This version should highlight cells starting with unrecognized characters or a newline:

Sub Dimitris2()
Dim cell As Range, weird As Boolean, cnt&
cnt = 0
For Each cell In ActiveSheet.UsedRange
    weird = False
    If Not IsError(cell) Then
        If Len(cell) > 0 Then
            Select Case Evaluate("=trim(code(" & cell.Address & "))")
                Case 63                     ' question mark
                    weird = True
                Case 10                     ' new line
                    weird = True
            End Select
            If weird Then
                cell.Interior.Color = RGB(250, 250, 5)
                cnt = cnt + 1
            End If
        End If
    End If
Next
MsgBox cnt & " cells were highlighted."
End Sub

 

[Dimitris254]

this works Wolf, but highlights only 2 cells (out of 4k) - which is expected, since most non-ascii chars and newlines are in the cell, not in start of it

i think the aforementioned thread could be part of the logic:

Sub highlight_nonascii()

Dim cell As Range
Dim weird As Boolean
Dim cnt As Integer

cnt = 0

For Each cell In ActiveSheet.UsedRange
    weird = False
    If Not IsError(cell) Then
        If Len(cell) > 0 Then
             Select Case Evaluate("=trim(code(" & cell.Address & "))")
            Select Case [evaluate each character in the cell for non-ascii and newline]
                Case 63                     ' question mark ' non-ascii char
                    weird = True
                Case 10                     ' new line
                    weird = True
            End Select
            If weird Then
                cell.Interior.Color = RGB(250, 250, 5)
                cnt = cnt + 1
            End If
        End If
    End If
Next
MsgBox cnt & " cells were highlighted."
End Sub

Note that i removed the case for question mark; question marks are generated in the CSV, not the xlsm. The point is to correct the non-ascii characters to ascii ones, before generating the CSV (otherwise the ???? is lost information).

 

[Worf]

• Indeed, the CODE function only looks at the cell’s first character.
• The macro is not searching for question marks; the CODE function returns 63, which is the “?” character, when it encounters unrecognizable stuff, like Chinese or Arabic.
• I will write a version that searches the whole cells; it will probably run slowly.

 

[Dimitris254]

no need to put more time into this Worf, i asked for help over excelforum and jindon's macro seems to work!

thank you for the time put again, i will follow up if anything pops up down the road