• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Module is not appropriately identifying blank cells with spaces

RDii

New Member
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!
 

Attachments

  • Identify Blank Cells Test.xlsm
    20.4 KB · Views: 3
RDii
Those 'missing blanks' Your named 'spaces' are not 'spaces' Asc(32)!
Those are Asc(202) and trim won't matter of those!
 
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:
Code:
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
 
Last edited:
Back
Top