Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem Unhidding columns/rows in excel using VBA
I am having trouble with some VBA code. The goal was to write code that
would hide all columns or rows and then unhide a select few. Here was the first attempt: Sub UnhideCol() With ActiveSheet .Columns.Hidden = True 'Hide all columns .Columns("A:B").Hidden = False 'Unhide some range of columns End With End Sub The result is that all columns hidden instead of having ("A:B") unhidden. Here is another try: Sub UnhideCol2() With ActiveSheet .Range("A:B").Columns.Hidden = True Set MyRange = .Cells.SpecialCells(xlVisible) 'MYRange("C:IV") .Columns.Hidden = True 'Hide all columns MyRange.Columns.Hidden = False 'Unhide MyRange("C:IV") End With End Sub This successfully unhides ("C:IV"). Here is the same macro with only the range changed from ("A:B") to ("C:IV"). Sub UnhideCol2() With ActiveSheet .Range("C:IV").Columns.Hidden = True Set MyRange = .Cells.SpecialCells(xlVisible) 'MYRange("A:B") .Columns.Hidden = True 'Hide all columns MyRange.Columns.Hidden = False 'Unhide MyRange("A:B") End With End Sub The result is that all columns are hidden rather than having ("A:B") unhidden. There seems to be a lot of inconsistency with how VBA/Excel are handling the code. It is even stranger with Rows: Sub UnhideRow() With ActiveSheet .Rows.Hidden = True 'Hide all rows .Rows("1:2").Hidden = False 'Unhide some range of rows End With End Sub This creates a run-time error '-2147417848 (80010108)' Method 'Hidden' of object 'Range' failed I am using Excel 2000, but it the run-time error occured when I tested it in Excel 2003, too. Any insight would be greatly appreciated. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem Unhidding columns/rows in excel using VBA
The first attempt worked for me. I did have to scroll to the left to see
columns A:B, though. Dan Hatola wrote: I am having trouble with some VBA code. The goal was to write code that would hide all columns or rows and then unhide a select few. Here was the first attempt: Sub UnhideCol() With ActiveSheet .Columns.Hidden = True 'Hide all columns .Columns("A:B").Hidden = False 'Unhide some range of columns End With End Sub The result is that all columns hidden instead of having ("A:B") unhidden. Here is another try: Sub UnhideCol2() With ActiveSheet .Range("A:B").Columns.Hidden = True Set MyRange = .Cells.SpecialCells(xlVisible) 'MYRange("C:IV") .Columns.Hidden = True 'Hide all columns MyRange.Columns.Hidden = False 'Unhide MyRange("C:IV") End With End Sub This successfully unhides ("C:IV"). Here is the same macro with only the range changed from ("A:B") to ("C:IV"). Sub UnhideCol2() With ActiveSheet .Range("C:IV").Columns.Hidden = True Set MyRange = .Cells.SpecialCells(xlVisible) 'MYRange("A:B") .Columns.Hidden = True 'Hide all columns MyRange.Columns.Hidden = False 'Unhide MyRange("A:B") End With End Sub The result is that all columns are hidden rather than having ("A:B") unhidden. There seems to be a lot of inconsistency with how VBA/Excel are handling the code. It is even stranger with Rows: Sub UnhideRow() With ActiveSheet .Rows.Hidden = True 'Hide all rows .Rows("1:2").Hidden = False 'Unhide some range of rows End With End Sub This creates a run-time error '-2147417848 (80010108)' Method 'Hidden' of object 'Range' failed I am using Excel 2000, but it the run-time error occured when I tested it in Excel 2003, too. Any insight would be greatly appreciated. -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem Unhidding columns/rows in excel using VBA
Thanks for checking it out. I tried it on another computer and I had luck
with my first three subroutines (all column related). However, I am still getting the run-time error when I try to hide the rows. Sub UnhideRow() With ActiveSheet .Rows.Hidden = True 'Hide all rows .Rows("1:2").Hidden = False 'Unhide some range of rows End With End Sub "Dave Peterson" wrote: The first attempt worked for me. I did have to scroll to the left to see columns A:B, though. Dan Hatola wrote: I am having trouble with some VBA code. The goal was to write code that would hide all columns or rows and then unhide a select few. Here was the first attempt: Sub UnhideCol() With ActiveSheet .Columns.Hidden = True 'Hide all columns .Columns("A:B").Hidden = False 'Unhide some range of columns End With End Sub The result is that all columns hidden instead of having ("A:B") unhidden. Here is another try: Sub UnhideCol2() With ActiveSheet .Range("A:B").Columns.Hidden = True Set MyRange = .Cells.SpecialCells(xlVisible) 'MYRange("C:IV") .Columns.Hidden = True 'Hide all columns MyRange.Columns.Hidden = False 'Unhide MyRange("C:IV") End With End Sub This successfully unhides ("C:IV"). Here is the same macro with only the range changed from ("A:B") to ("C:IV"). Sub UnhideCol2() With ActiveSheet .Range("C:IV").Columns.Hidden = True Set MyRange = .Cells.SpecialCells(xlVisible) 'MYRange("A:B") .Columns.Hidden = True 'Hide all columns MyRange.Columns.Hidden = False 'Unhide MyRange("A:B") End With End Sub The result is that all columns are hidden rather than having ("A:B") unhidden. There seems to be a lot of inconsistency with how VBA/Excel are handling the code. It is even stranger with Rows: Sub UnhideRow() With ActiveSheet .Rows.Hidden = True 'Hide all rows .Rows("1:2").Hidden = False 'Unhide some range of rows End With End Sub This creates a run-time error '-2147417848 (80010108)' Method 'Hidden' of object 'Range' failed I am using Excel 2000, but it the run-time error occured when I tested it in Excel 2003, too. Any insight would be greatly appreciated. -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem Unhidding columns/rows in excel using VBA
I've never had real good luck with hiding and unhiding rows/columns.
The usedrange seems to grow with each iteration. I think I wouldn't do this. But this did work for me: Option Explicit Sub UnhideRow() With ActiveSheet .Rows("1:2").Hidden = False .Rows("3:" & .Rows.Count).Hidden = True End With End Sub Dan Hatola wrote: Thanks for checking it out. I tried it on another computer and I had luck with my first three subroutines (all column related). However, I am still getting the run-time error when I try to hide the rows. Sub UnhideRow() With ActiveSheet .Rows.Hidden = True 'Hide all rows .Rows("1:2").Hidden = False 'Unhide some range of rows End With End Sub "Dave Peterson" wrote: The first attempt worked for me. I did have to scroll to the left to see columns A:B, though. Dan Hatola wrote: I am having trouble with some VBA code. The goal was to write code that would hide all columns or rows and then unhide a select few. Here was the first attempt: Sub UnhideCol() With ActiveSheet .Columns.Hidden = True 'Hide all columns .Columns("A:B").Hidden = False 'Unhide some range of columns End With End Sub The result is that all columns hidden instead of having ("A:B") unhidden. Here is another try: Sub UnhideCol2() With ActiveSheet .Range("A:B").Columns.Hidden = True Set MyRange = .Cells.SpecialCells(xlVisible) 'MYRange("C:IV") .Columns.Hidden = True 'Hide all columns MyRange.Columns.Hidden = False 'Unhide MyRange("C:IV") End With End Sub This successfully unhides ("C:IV"). Here is the same macro with only the range changed from ("A:B") to ("C:IV"). Sub UnhideCol2() With ActiveSheet .Range("C:IV").Columns.Hidden = True Set MyRange = .Cells.SpecialCells(xlVisible) 'MYRange("A:B") .Columns.Hidden = True 'Hide all columns MyRange.Columns.Hidden = False 'Unhide MyRange("A:B") End With End Sub The result is that all columns are hidden rather than having ("A:B") unhidden. There seems to be a lot of inconsistency with how VBA/Excel are handling the code. It is even stranger with Rows: Sub UnhideRow() With ActiveSheet .Rows.Hidden = True 'Hide all rows .Rows("1:2").Hidden = False 'Unhide some range of rows End With End Sub This creates a run-time error '-2147417848 (80010108)' Method 'Hidden' of object 'Range' failed I am using Excel 2000, but it the run-time error occured when I tested it in Excel 2003, too. Any insight would be greatly appreciated. -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Stop rows that i have hidden unhidding when i filter in excel | Excel Worksheet Functions | |||
Excel 2003 - change columns to rows and rows to columns | Excel Discussion (Misc queries) | |||
Excel 2003 - change columns to rows and rows to columns | Excel Discussion (Misc queries) | |||
Very slow unhidding rows Excel 2000 SP3 | Excel Programming | |||
Very slow unhidding rows Excel 2000 SP3 | Excel Programming |