Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unhide 1 column at a time from named range
Hello,
I have a named range of columns that are hidden. I would like a macro that will unhide the left-most column (that is hidden) in the range when a button is clicked. For instance, clicking the button once would unhide column 1 of range. Clicking button again would unhide column 2 of hidden range, and so forth. I greatly appreciate any help! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unhide 1 column at a time from named range
One way:
Option Explicit Sub testme() Dim iCol As Long Dim myRng As Range Dim UnhidACol As Boolean With Worksheets("sheet1") Set myRng = .Range("myrangenamehere") End With With myRng.Areas(1) UnhidACol = False For iCol = 1 To .Columns.Count If .Columns(iCol).EntireColumn.Hidden = True Then .Columns(iCol).EntireColumn.Hidden = False UnhidACol = True 'get out Exit For End If Next iCol End With If UnhidACol = False Then Beep End If End Sub blonde1030 wrote: Hello, I have a named range of columns that are hidden. I would like a macro that will unhide the left-most column (that is hidden) in the range when a button is clicked. For instance, clicking the button once would unhide column 1 of range. Clicking button again would unhide column 2 of hidden range, and so forth. I greatly appreciate any help! -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unhide 1 column at a time from named range
Something like:
Sub ShowOneByOne() Dim c As Range For Each c In Application.Intersect(Range("HiddenRange"), Rows(1)) If c.EntireColumn.Hidden Then Exit For Next On Error Resume Next c.EntireColumn.Hidden = False On Error GoTo 0 End Sub There's probably a better way but this should work. __________________________________________________ ______________________ "blonde1030" wrote in message ... Hello, I have a named range of columns that are hidden. I would like a macro that will unhide the left-most column (that is hidden) in the range when a button is clicked. For instance, clicking the button once would unhide column 1 of range. Clicking button again would unhide column 2 of hidden range, and so forth. I greatly appreciate any help! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unhide 1 column at a time from named range
Dave, do you need the ".EntireColumn" property the way you have written the
code? __________________________________________________ ______________________ "Dave Peterson" wrote in message ... One way: Option Explicit Sub testme() Dim iCol As Long Dim myRng As Range Dim UnhidACol As Boolean With Worksheets("sheet1") Set myRng = .Range("myrangenamehere") End With With myRng.Areas(1) UnhidACol = False For iCol = 1 To .Columns.Count If .Columns(iCol).EntireColumn.Hidden = True Then .Columns(iCol).EntireColumn.Hidden = False UnhidACol = True 'get out Exit For End If Next iCol End With If UnhidACol = False Then Beep End If End Sub blonde1030 wrote: Hello, I have a named range of columns that are hidden. I would like a macro that will unhide the left-most column (that is hidden) in the range when a button is clicked. For instance, clicking the button once would unhide column 1 of range. Clicking button again would unhide column 2 of hidden range, and so forth. I greatly appreciate any help! -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unhide 1 column at a time from named range
Welcome back Vasant!
Just in case that HiddenRange doesn't include row 1, I'd use something like: With worksheets("sheet1") '<- I like to specify the worksheet For Each c In _ Application.Intersect(.Range("HiddenRange").entire column, .Rows(1)) ..... End with End Sub Vasant Nanavati wrote: Something like: Sub ShowOneByOne() Dim c As Range For Each c In Application.Intersect(Range("HiddenRange"), Rows(1)) If c.EntireColumn.Hidden Then Exit For Next On Error Resume Next c.EntireColumn.Hidden = False On Error GoTo 0 End Sub There's probably a better way but this should work. __________________________________________________ ______________________ "blonde1030" wrote in message ... Hello, I have a named range of columns that are hidden. I would like a macro that will unhide the left-most column (that is hidden) in the range when a button is clicked. For instance, clicking the button once would unhide column 1 of range. Clicking button again would unhide column 2 of hidden range, and so forth. I greatly appreciate any help! -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unhide 1 column at a time from named range
Good point, but since the OP said it was a "range of columns", I assumed
that row 1 would be included. :-) And thanks for the welcome; it's nice to "see" all the old "faces." I hope to be here more consistently (although it'sll probably take me a while to get up to speed!). __________________________________________________ ______________________ "Dave Peterson" wrote in message ... Welcome back Vasant! Just in case that HiddenRange doesn't include row 1, I'd use something like: With worksheets("sheet1") '<- I like to specify the worksheet For Each c In _ Application.Intersect(.Range("HiddenRange").entire column, .Rows(1)) .... End with End Sub Vasant Nanavati wrote: Something like: Sub ShowOneByOne() Dim c As Range For Each c In Application.Intersect(Range("HiddenRange"), Rows(1)) If c.EntireColumn.Hidden Then Exit For Next On Error Resume Next c.EntireColumn.Hidden = False On Error GoTo 0 End Sub There's probably a better way but this should work. __________________________________________________ ______________________ "blonde1030" wrote in message ... Hello, I have a named range of columns that are hidden. I would like a macro that will unhide the left-most column (that is hidden) in the range when a button is clicked. For instance, clicking the button once would unhide column 1 of range. Clicking button again would unhide column 2 of hidden range, and so forth. I greatly appreciate any help! -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unhide 1 column at a time from named range
Works great! Thank you!
"Dave Peterson" wrote: One way: Option Explicit Sub testme() Dim iCol As Long Dim myRng As Range Dim UnhidACol As Boolean With Worksheets("sheet1") Set myRng = .Range("myrangenamehere") End With With myRng.Areas(1) UnhidACol = False For iCol = 1 To .Columns.Count If .Columns(iCol).EntireColumn.Hidden = True Then .Columns(iCol).EntireColumn.Hidden = False UnhidACol = True 'get out Exit For End If Next iCol End With If UnhidACol = False Then Beep End If End Sub blonde1030 wrote: Hello, I have a named range of columns that are hidden. I would like a macro that will unhide the left-most column (that is hidden) in the range when a button is clicked. For instance, clicking the button once would unhide column 1 of range. Clicking button again would unhide column 2 of hidden range, and so forth. I greatly appreciate any help! -- Dave Peterson |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unhide 1 column at a time from named range
Nope. But it doesn't hurt and I kind of like the way it documents the code.
(But I'm a bit strange.) Vasant Nanavati wrote: Dave, do you need the ".EntireColumn" property the way you have written the code? __________________________________________________ ______________________ "Dave Peterson" wrote in message ... One way: Option Explicit Sub testme() Dim iCol As Long Dim myRng As Range Dim UnhidACol As Boolean With Worksheets("sheet1") Set myRng = .Range("myrangenamehere") End With With myRng.Areas(1) UnhidACol = False For iCol = 1 To .Columns.Count If .Columns(iCol).EntireColumn.Hidden = True Then .Columns(iCol).EntireColumn.Hidden = False UnhidACol = True 'get out Exit For End If Next iCol End With If UnhidACol = False Then Beep End If End Sub blonde1030 wrote: Hello, I have a named range of columns that are hidden. I would like a macro that will unhide the left-most column (that is hidden) in the range when a button is clicked. For instance, clicking the button once would unhide column 1 of range. Clicking button again would unhide column 2 of hidden range, and so forth. I greatly appreciate any help! -- Dave Peterson -- Dave Peterson |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unhide 1 column at a time from named range
I haven't changed. I don't always read the whole post--or read it the way I
want to. And just to give you a leg up, have this in your clipboard: Tools|Options|General tab|Uncheck R1C1 Reference style (some questions don't change <vbg.) Vasant Nanavati wrote: Good point, but since the OP said it was a "range of columns", I assumed that row 1 would be included. :-) And thanks for the welcome; it's nice to "see" all the old "faces." I hope to be here more consistently (although it'sll probably take me a while to get up to speed!). __________________________________________________ ______________________ "Dave Peterson" wrote in message ... Welcome back Vasant! Just in case that HiddenRange doesn't include row 1, I'd use something like: With worksheets("sheet1") '<- I like to specify the worksheet For Each c In _ Application.Intersect(.Range("HiddenRange").entire column, .Rows(1)) .... End with End Sub Vasant Nanavati wrote: Something like: Sub ShowOneByOne() Dim c As Range For Each c In Application.Intersect(Range("HiddenRange"), Rows(1)) If c.EntireColumn.Hidden Then Exit For Next On Error Resume Next c.EntireColumn.Hidden = False On Error GoTo 0 End Sub There's probably a better way but this should work. __________________________________________________ ______________________ "blonde1030" wrote in message ... Hello, I have a named range of columns that are hidden. I would like a macro that will unhide the left-most column (that is hidden) in the range when a button is clicked. For instance, clicking the button once would unhide column 1 of range. Clicking button again would unhide column 2 of hidden range, and so forth. I greatly appreciate any help! -- Dave Peterson -- Dave Peterson |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unhide 1 column at a time from named range
Yep, I guess I'll have to pay my dues again and work myself up from the
Tools | Options questions! <g __________________________________________________ _______________________ "Dave Peterson" wrote in message ... I haven't changed. I don't always read the whole post--or read it the way I want to. And just to give you a leg up, have this in your clipboard: Tools|Options|General tab|Uncheck R1C1 Reference style (some questions don't change <vbg.) Vasant Nanavati wrote: Good point, but since the OP said it was a "range of columns", I assumed that row 1 would be included. :-) And thanks for the welcome; it's nice to "see" all the old "faces." I hope to be here more consistently (although it'sll probably take me a while to get up to speed!). __________________________________________________ ______________________ "Dave Peterson" wrote in message ... Welcome back Vasant! Just in case that HiddenRange doesn't include row 1, I'd use something like: With worksheets("sheet1") '<- I like to specify the worksheet For Each c In _ Application.Intersect(.Range("HiddenRange").entire column, .Rows(1)) .... End with End Sub Vasant Nanavati wrote: Something like: Sub ShowOneByOne() Dim c As Range For Each c In Application.Intersect(Range("HiddenRange"), Rows(1)) If c.EntireColumn.Hidden Then Exit For Next On Error Resume Next c.EntireColumn.Hidden = False On Error GoTo 0 End Sub There's probably a better way but this should work. __________________________________________________ ______________________ "blonde1030" wrote in message ... Hello, I have a named range of columns that are hidden. I would like a macro that will unhide the left-most column (that is hidden) in the range when a button is clicked. For instance, clicking the button once would unhide column 1 of range. Clicking button again would unhide column 2 of hidden range, and so forth. I greatly appreciate any help! -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Unhide column if range of cells contain data | Excel Worksheet Functions | |||
How do I specify column for named range of rows | Charts and Charting in Excel | |||
Picking out column in named range | Excel Worksheet Functions | |||
Working with one Column in a Named Range | Excel Programming | |||
Checkbox to hide/unhide named range | Excel Programming |