Check If Any Cell In A Range Has A Value

Question

I'm having trouble with confirming if any cell in a specified range contains any value. Hoping someone can help me out with the syntax.

Thanks in advance

        For i = 1 To DataRange.Rows.Count
            CheckCells = If(Qty > 0 And WS1.Range("Sheet1!A" & i & ":Sheet1!Z" & i).Value <> "", "HasValue", "NoValue")
        Next i

Thanks for your comment @SpencerBarnes but this doesn’t seem to resolve the problem. — Patrick, Sep 06 '21 at 09:55
1) Does it give an error message? 2) You should use `AndAlso` instead of `And`: [What is the difference between And and AndAlso in VB.NET?](https://stackoverflow.com/q/302047/1115360) 3) You might need `DirectCast(WS1.Range("Sheet1!A" & i & ":Sheet1!Z" & i), Excel.Range).Value`. — Andrew Morton, Sep 06 '21 at 12:16
@SpencerBarnes That should be done if and only if the code is VBA. If it's VB.NET, then `If(...` is by far the preferred approach as it's a built-in operator with type safety and short-circuiting vs. a library function with `Object` parameters and full evaluation of its arguments. — Craig, Sep 07 '21 at 13:44

score 0 · Answer 1 · answered Sep 06 '21 at 17:44

You could iterate over the cells to examine each one:

Dim r = xl.Range("A1", "D4")
Dim nCells = r.Cells.Count
Dim isAllBlank = True

For i = 1 To nCells
    If DirectCast(r.Cells(i), Excel.Range).Value IsNot Nothing Then
        isAllBlank = False
        Exit For
    End If
Next

checkCells = If(isAllBlank, "NoValue", "HasValue")

Where xl.Range is the range you need to check.

I tried to use range.SpecialCells(Excel.XlCellType.xlCellTypeBlanks), but it was a bit moody about it.

score 0 · Accepted Answer · answered Sep 14 '21 at 03:39

I was able to achieve what I wanted by using the following code.

                For i = 1 To DataRange.Rows.Count
                    If xlApp.WorksheetFunction.CountA(WS.Range("Parts!Z" & R & ":Parts!AI" & R)) > 0 Then
                        CheckCells = "HasValue"
                    Else
                        CheckCells = "NoValue"
                    End If
                Next i

Check If Any Cell In A Range Has A Value

2 Answers2