Check If Any Cell In A Range Has A Value-CodePudding

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

CodePudding user response：

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.

CodePudding user response：

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