Help finding error setting range to Visible Cells

My code loops through a series of named ranges. If there is an "X" in Col A
of any row of the range, that row is hidden. A new range is then set to the
visible cells, and then the rows are unhidden. My ranges are named "rng1",
"rng2", "rng3", "rng4", and "rngC". With XL2000, I'm using
Set rngVis = Range(strName).SpecialCells(xlCellTypeVisible)
where strName is the range name.

This works fine except in two instances, one expected, and one confusing.
If a range has all rows "X"ed out, meaning all rows get hidden, trying to
set a new range to Visible Cells gives an error 1004 - "Can't find any
cells". With Tom's help, I'm working on that.

But I also get error 1004 - "Can't find any cells" on rngC, whether any rows
are hidden or not. I can select rngC, both by code using strName and by the
Name box on the worksheet, and it is about five rows by 20 columns. But I
can't select the Visible Cells without an error.

Where can I begin looking for the reason for this?

Ed

Could you have something like 'Maxwell' in a cell that's tripping the 'X'
hide me?

"Ed" wrote:

> My code loops through a series of named ranges. If there is an "X" in Col A
> of any row of the range, that row is hidden. A new range is then set to the
> visible cells, and then the rows are unhidden. My ranges are named "rng1",
> "rng2", "rng3", "rng4", and "rngC". With XL2000, I'm using
> Set rngVis = Range(strName).SpecialCells(xlCellTypeVisible)
> where strName is the range name.
>
> This works fine except in two instances, one expected, and one confusing.
> If a range has all rows "X"ed out, meaning all rows get hidden, trying to
> set a new range to Visible Cells gives an error 1004 - "Can't find any
> cells". With Tom's help, I'm working on that.
>
> But I also get error 1004 - "Can't find any cells" on rngC, whether any rows
> are hidden or not. I can select rngC, both by code using strName and by the
> Name box on the worksheet, and it is about five rows by 20 columns. But I
> can't select the Visible Cells without an error.
>
> Where can I begin looking for the reason for this?
>
> Ed
>
>
>

Hi, Gary.

In the range that's giving me fits, it doesn't matter if I hide one row, all
rows, or no rows - it still gives me the "No visible cells found" error.
It's the same size as the other ranges.

Also, all these ranges were created and named by a macro that iterated
through the worksheet, taking the range name from the value in a cell; the
others were "1" through "4", and this one was "C"; the value was put in a
string and the name set up as strName = "rng" & strCell. So this range
wasn't created any differently than the others, either.

Any suggestions as to where to find the cause of the error are greatly
appreciated.

Ed

"Gary Brown" <[email protected]> wrote in message
news:[email protected]...
> Could you have something like 'Maxwell' in a cell that's tripping the 'X'
> hide me?
>
> "Ed" wrote:
>
> > My code loops through a series of named ranges. If there is an "X" in
Col A
> > of any row of the range, that row is hidden. A new range is then set to
the
> > visible cells, and then the rows are unhidden. My ranges are named
"rng1",
> > "rng2", "rng3", "rng4", and "rngC". With XL2000, I'm using
> > Set rngVis = Range(strName).SpecialCells(xlCellTypeVisible)
> > where strName is the range name.
> >
> > This works fine except in two instances, one expected, and one
confusing.
> > If a range has all rows "X"ed out, meaning all rows get hidden, trying
to
> > set a new range to Visible Cells gives an error 1004 - "Can't find any
> > cells". With Tom's help, I'm working on that.
> >
> > But I also get error 1004 - "Can't find any cells" on rngC, whether any
rows
> > are hidden or not. I can select rngC, both by code using strName and by
the
> > Name box on the worksheet, and it is about five rows by 20 columns. But
I
> > can't select the Visible Cells without an error.
> >
> > Where can I begin looking for the reason for this?
> >
> > Ed
> >
> >
> >

The error was me!! (I'm not surprised! 8>{ )

In the iteration through the range names, the numeric ranges (rng1 - rng4)
came first. If I had an error, I detected it with
If Err.Number <> 0 Then
MsgBox Err.Number
MsgBox Err.Description
End If
Next in the iteration was rngC. It would throw the same error! Suddenly,
it hit me - I never CLEARED the PREVIOUS error! It wasn't detecting a new
error - it was telling me my error number and description were still
unchanged!

I added Err.Clear after the second MsgBox and voila - no errors! Now I feel
dumb! Relieved, but still dumb!

Ed

"Ed" <ed_millis@NO_SPAM.yahoo.com> wrote in message
news:[email protected]...
> Hi, Gary.
>
> In the range that's giving me fits, it doesn't matter if I hide one row,
all
> rows, or no rows - it still gives me the "No visible cells found" error.
> It's the same size as the other ranges.
>
> Also, all these ranges were created and named by a macro that iterated
> through the worksheet, taking the range name from the value in a cell; the
> others were "1" through "4", and this one was "C"; the value was put in a
> string and the name set up as strName = "rng" & strCell. So this range
> wasn't created any differently than the others, either.
>
> Any suggestions as to where to find the cause of the error are greatly
> appreciated.
>
> Ed
>
> "Gary Brown" <[email protected]> wrote in message
> news:[email protected]...
> > Could you have something like 'Maxwell' in a cell that's tripping the
'X'
> > hide me?
> >
> > "Ed" wrote:
> >
> > > My code loops through a series of named ranges. If there is an "X" in
> Col A
> > > of any row of the range, that row is hidden. A new range is then set
to
> the
> > > visible cells, and then the rows are unhidden. My ranges are named
> "rng1",
> > > "rng2", "rng3", "rng4", and "rngC". With XL2000, I'm using
> > > Set rngVis = Range(strName).SpecialCells(xlCellTypeVisible)
> > > where strName is the range name.
> > >
> > > This works fine except in two instances, one expected, and one
> confusing.
> > > If a range has all rows "X"ed out, meaning all rows get hidden, trying
> to
> > > set a new range to Visible Cells gives an error 1004 - "Can't find any
> > > cells". With Tom's help, I'm working on that.
> > >
> > > But I also get error 1004 - "Can't find any cells" on rngC, whether
any
> rows
> > > are hidden or not. I can select rngC, both by code using strName and
by
> the
> > > Name box on the worksheet, and it is about five rows by 20 columns.
But
> I
> > > can't select the Visible Cells without an error.
> > >
> > > Where can I begin looking for the reason for this?
> > >
> > > Ed
> > >
> > >
> > >
>
>