VBA Return True if Value of cell is Blank (Null) not Empty

[brickhaus]

Hi All. I'm pretty much a novice - this seems like it should be easy but haven't been able to figure it out...

Here's what I'm working on - it hides rows based on value of column 3

Sub HURowsMaterialOrder()    BeginRow = 2
    EndRow = 101
    ChkCol = 3


    For RowCnt = BeginRow To EndRow
        If Cells(RowCnt, ChkCol).[COLOR=#ff0000]Value = "" [/COLOR]Then
            Cells(RowCnt, ChkCol).EntireRow.Hidden = True
        Else
            Cells(RowCnt, ChkCol).EntireRow.Hidden = False
        End If
    Next RowCnt
End Sub

It works fine if cell is "empty" - no value, but doesn't work if it's "blank" - contains formula with null product "". I need it to answer True to Null...

Any help would be much appreciated...

Thanks!

 

[steve the fish]

It should work for either so check you arent using " " with a space for example.

 

[brickhaus]

Thank you for responding.

No there isn't a space... here's an example of what's in the cell that's being checked:

=IFERROR(VLOOKUP(B4,'Costing Reference'!$B$1:$K$111,7, FALSE),"")

the VLOOKUP is returning a blank cell and therefore the IFERROR should be returning a ""

If I delete the formula (ie make it empty) then the macro works and hides the row...

 

[steve the fish]

You arent invoking the IFERROR if the VLOOKUP is finding its lookup value. See what this produces if you put it in a cell:

=VLOOKUP(B4,'Costing Reference'!$B$1:$K$111,7, FALSE)

If its referencing a blank cell it should produce 0 if the lookup value is being found. If the formula produces a blank then theres probably a space in that cell.

 

[brickhaus]

The VLOOKUP doesn't find a value there most of the time and usually this generates an error code (#VALUE!, etc) that's why I make a habit of using IFERROR... the only time the cell produces a response is when it finds its lookup value.

I've tried just with VLOOKUP and the same thing continues to happen. When there's a value in the 7th column it finds it, although strangely it's not coming up with any type of error when the cell is blank...

I've also tried using INDEX(MATCH) and it produces the same result...

 

[JoeMo]

The code you posted in your OP should be hiding rows where the col C value is the null string. That includes both truly blank cells and cells with a formula that returns "" (the null string). It works fine for me. In an empty cell if you enter =LEN(C3) where C3 is replaced by a col C cell that has a formula that you believe is returning "", what do you get?