Module is not appropriately identifying blank cells with spaces

[RDii]

Hello all,

I am working on a project that is supposed to search through a list of States provided by another program and identify which region it is associated with. Sometimes the State field contains invisible values, that appear to be spaces. I am attempting to identify those values and label them as "blank", however the TRIM function isn't working as expected.

Can you please look at module 1 to see if you can help me see what adjustments would help identify these "empty" cells.

I have attached the spreadsheet below.

Thank you!

 

[vletm]

RDii
Those 'missing blanks' Your named 'spaces' are not 'spaces' Asc(32)!
Those are Asc(202) and trim won't matter of those!

 

[p45cal]

3 Points:
1. vba's Trim function only takes spaces off the beginning and end of a string. The worksheet function's TRIM also reduces multiple spaces within a string down to a single space. Probably not relevant for state abbreviations, but we'll use the worksheet function Trim here instead.
2. You have non-breaking spaces (code 160) in your strings which are not cleared by any Trim function. Nor are they cleared by the worksheet function CLEAN. You have to remove/replace these explicitly. We'll replace them with spaces (code 32).
3. Your code contains this:
y.Offset(o, 1).Value
where the o is the letter o and should be the value 0 (zero) (or miss it out altogether).

So you could end up with a good chance of the result you're looking for with:

Sub FindTheBlankStates2()
  For Each y In Sheets("Sheet1").Range("A2:A14")
    If Application.Clean(Application.Trim(Replace(y.Value, Chr(160), " "))) = "" Then
      y.Offset(, 1).Value = "blank"
    Else
      y.Offset(, 1).Value = "not blank"
    End If
  Next y
End Sub

 

[RDii]

Thank you so much! This is exactly what I needed.