Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help finding error setting range to Visible Cells
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help finding error setting range to Visible Cells
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" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help finding error - FOUND IT!
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" wrote in message ... 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" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I include only visible cells in a range? | Excel Worksheet Functions | |||
Select only visible cells in a selected range | Excel Programming | |||
Trying to select the visible range of cells | Excel Programming | |||
Error stepping through Autofiltered visible range | Excel Programming | |||
error with setting "Items.Visible = True" | Excel Programming |