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
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