Unhide 1 column at a time from named range

[blonde1030]

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]

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

 

[Vasant Nanavati]

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.
________________________________________________________________________

 

[Vasant Nanavati]

Dave, do you need the ".EntireColumn" property the way you have written the
code?
________________________________________________________________________

 

[Dave Peterson]

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").entirecolumn, .Rows(1))

.....
End with
End Sub

 

[Vasant Nanavati]

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!).

________________________________________________________________________

 

[blonde1030]

Works great! Thank you!

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

 

[Dave Peterson]

Nope. But it doesn't hurt and I kind of like the way it documents the code.

(But I'm a bit strange.)

 

[Dave Peterson]

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]

Yep, I guess I'll have to pay my dues again and work myself up from the
Tools | Options questions! <g>

_________________________________________________________________________