Detect NULL (??) range?
 

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

Detect NULL (??) range?
 

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.


--
Regards,
Tom Ogilvy



"Ed" wrote in message
...
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

Detect NULL (??) range?
 

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" wrote in message
...
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.


--
Regards,
Tom Ogilvy



"Ed" wrote in message
...
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

Detect NULL (??) range?
 

' 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


"Ed" wrote in message
...
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" wrote in message
...
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.


--
Regards,
Tom Ogilvy



"Ed" wrote in message
...
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

Detect NULL (??) range?
 

Thank you, Tom! That's perfect!

Ed

"Tom Ogilvy" wrote in message
...
' 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


"Ed" wrote in message
...
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" wrote in message
...
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.


--
Regards,
Tom Ogilvy



"Ed" wrote in message
...
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