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

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" <ed_millis@NO_SPAM.yahoo.com> wrote in message
news:[email protected]...
> 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
>
>

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <ed_millis@NO_SPAM.yahoo.com> wrote in message
> news:[email protected]...
> > 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
> >
> >
>
>

' 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" <ed_millis@NO_SPAM.yahoo.com> wrote in message
news:%[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <ed_millis@NO_SPAM.yahoo.com> wrote in message
> > news:[email protected]...
> > > 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
> > >
> > >
> >
> >
>
>

Thank you, Tom! That's perfect!

Ed

"Tom Ogilvy" <[email protected]> wrote in message
news:[email protected]...
> ' 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" <ed_millis@NO_SPAM.yahoo.com> wrote in message
> news:%[email protected]...
> > 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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" <ed_millis@NO_SPAM.yahoo.com> wrote in message
> > > news:[email protected]...
> > > > 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
> > > >
> > > >
> > >
> > >
> >
> >
>
>