test range contents

glenninflorida

New Member
Joined
Oct 20, 2005
Messages
4
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
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

BrianB

Well-known Member
Joined
Feb 17, 2003
Messages
8,127
This is one way. Bear in mind that a cell containing a zero is not blank.
Code:
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

New Member
Joined
Oct 20, 2005
Messages
4
You Rock Brian!

Thank you sooo much!!!

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

Have a great day!
 

Watch MrExcel Video

Forum statistics

Threads
1,129,553
Messages
5,636,989
Members
416,953
Latest member
broexc

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top