test range contents

[glenninflorida]

Hi all:

I am trying to test if a range of cells has any contents (can be alpha or numeric)


I have attempted:

If IsEmpty(ActiveSheet.Range("B214:K214")) Then
MsgBox ("all clear")
Else
MsgBox ("still another cell has a value")
End If

AND:

If ActiveSheet.Range("B214:K214").SpecialCells(xlCellTypeBlanks) Is Nothing Then ...

It keeps thinking there is contents in the cells. Since an alpha is permissible, I can't simply check the sum of the range for a value.


Any ideas?


Thanks,
Glenn




FYI:

I'm not sure if it matters, but this is part of a larger sub that hides/unhides rows based on a "header" value being entered. All works great if I only have the hiding / unhiding of rows based on target cell contents.

However, my additional test should see if another column has a header value and not hide the entirerows if there are other header values (and values beneath) being used.

based on worksheet_change event:

For i = 1 To lastRow Step 1
Set rngData = ActiveSheet.Range(Range("F1068:H1092").Item(i, 1).Value)
rStart = Range("F1068:H1092").Item(i, 2).Value
rEnd = Range("F1068:H1092").Item(i, 3).Value
rRange = "" & rStart + 1 & ":" & rEnd & ""

If Not Application.Intersect(rngData, Target) Is Nothing Then
If IsEmpty(Target.Value) Then
MsgBox ("cell contents deleted")

If IsEmpty(ActiveSheet.Range("B214:K214")) Then
MsgBox ("BAM! hide detail rows: " & rRange)
Else
MsgBox ("still another cell has a value - don't hide detail rows")
End If
Else
MsgBox ("cell added")

MsgBox ("unhide detail rows: " & rRange)
End If
End If
Set rngData = Nothing
Next i

 

[BrianB]

This is one way. Bear in mind that a cell containing a zero is not blank.

Sub test()
    Dim MyRange As Range
    Set MyRange = ActiveSheet.Range("B214:K214")
    If MyRange.SpecialCells(xlCellTypeBlanks).Count _
        <> MyRange.Cells.Count Then
        MsgBox ("Still contains values")
    Else
        MsgBox ("all clear")
    End If
End Sub

 

[glenninflorida]

You Rock Brian!

Thank you sooo much!!!

That did it - and fixed another issue I didn't realize I had!

Have a great day!