Detect NULL (??) range?

[Ed]

My code detects an "X" in one column of a range; if present, that row is
hidden, and a new range is set to the intersection of that range and the
visible cells only of that range. Underneath is the formula
=COUNT((col7 rng1Work))
which counts the number of items in the intersection of the column range and
the visible cells range.

It all works fine - until I "X" out all five rows of the original range.
Then the COUNT doesn't change from whatever it was previously. I'm assuming
that since it hides all the rows, and there are no visible cells to
intersect with, the INTERSECT function fails, and my On Error Resume Next
flows right over it without burping - and without resetting my range.

How can I detect a NULL (?? correct term??) range, or the lack of a range,
when this occurs? (Note: the original range covers 5 rows, but this can
vary.)

Ed

' Hide rows with value of "X"
For i = 1 To rngThis.Rows.Count
If rngThis.Cells(i, 1).Value = "X" Or _
rngThis.Cells(i, 1).Value = "x" Then
rngThis.Rows(i).Hidden = True
End If
Next i

' Reset range to visible cells only
Range(strName).SpecialCells(xlVisible).Select
Set rngVis = Selection
Set rngThis = Application.Intersect(Range(strName), rngVis)
Range(strName).Rows.Hidden = False
rngThis.Name = strName & "Work"

Calculate

 

[Tom Ogilvy]

Something like

Dim rng as Range

set rng = Range(strName).SpecialCells(xlVisible)
if rng is nothing then
MsgBox "Done - all not visible"
exit sub
end if
rng.select

You need to get away from selecting and activating anyway.

 

[Ed]

Thanks for the reply, Tom. I guess I didn't collect my thoughts into what I
really wanted to ask. I WANT to be able to set a "zero-value" range. If
all 5 rows are "X"ed out and there are no visible cells, and I intersect the
range with its visible cells, I *want* a range that will give me a COUNT of
0. I also use
=AVERAGE ((col7 rng1Work))
and
=STDEV ((col7 rng1Work))
and these must also have something besides error values if the range is zero
rows. What would you suggest?

Ed

 

[Tom Ogilvy]

' Hide rows with value of "X"
For i = 1 To rngThis.Rows.Count
If rngThis.Cells(i, 1).Value = "X" Or _
rngThis.Cells(i, 1).Value = "x" Then
rngThis.Rows(i).Hidden = True
End If
Next i

' Reset range to visible cells only
Range(strName).SpecialCells(xlVisible).Select
Set rngVis = Selection
if rngVis is nothing then
set rngVis = Intersect(range("col7"), Activesheet.UsedRange)
set rngVis =
rngVis.Offset(rngVis.Rows.count+2,0).Resize(1,1))
End if
Set rngThis = Application.Intersect(Range(strName), rngVis)
Range(strName).Rows.Hidden = False
rngThis.Name = strName & "Work"
Calculate

This should make rngVis an empty cell when all rows are hidden.

 

[Ed]

Thank you, Tom! That's perfect!

Ed

' Hide rows with value of "X"
For i = 1 To rngThis.Rows.Count
If rngThis.Cells(i, 1).Value = "X" Or _
rngThis.Cells(i, 1).Value = "x" Then
rngThis.Rows(i).Hidden = True
End If
Next i

' Reset range to visible cells only
Range(strName).SpecialCells(xlVisible).Select
Set rngVis = Selection
if rngVis is nothing then
set rngVis = Intersect(range("col7"), Activesheet.UsedRange)
set rngVis =
rngVis.Offset(rngVis.Rows.count+2,0).Resize(1,1))
End if
Set rngThis = Application.Intersect(Range(strName), rngVis)
Range(strName).Rows.Hidden = False
rngThis.Name = strName & "Work"
Calculate

This should make rngVis an empty cell when all rows are hidden.

--
Regards,
Tom Ogilvy


what row