Identify Hidden Columns

[Steve]

Hi All,

Is there a way to determine which columns are hidden (prior to a macro
execution, and may vary each time the macro is executed), then UNhide
those columns, execute the code, and finally REhide the original
columns that were hidden?

Thanks!

 

[GS]

Steve has brought this to us :

Hi All,

Is there a way to determine which columns are hidden (prior to a macro
execution, and may vary each time the macro is executed), then UNhide
those columns, execute the code, and finally REhide the original
columns that were hidden?

Thanks!

You can do this several ways, but they'd all follow the same concept;

Loop the columns in the range

If col.Hidden Then add its index to a delimited string
OR redim/add to an array

col.Hidden = False

do stuff...

Loop the array or delimited string and...
Columns(<LoopCounter>).Hidden = True

 

[isabelle]

hi Steve,

Sub Macro1()
Dim ColumnsList()
Dim x As Integer, i As Integer
x = 0
For i = 0 To 255 'or 16383  depend on ver.
If Columns(i + 1).Hidden = True Then
ReDim Preserve ColumnsList(x)
ColumnsList(x) = i + 1
Columns(i + 1).Hidden = False
x = x + 1
End If
Next

'your code

For i = LBound(ColumnsList) To UBound(ColumnsList)
Columns(ColumnsList(i)).Hidden = True
Next
End Sub

 

[GS]

isabelle explained :

hi Steve,

Sub Macro1()
Dim ColumnsList()
Dim x As Integer, i As Integer
x = 0
For i = 0 To 255 'or 16383  depend on ver.
If Columns(i + 1).Hidden = True Then
ReDim Preserve ColumnsList(x)
ColumnsList(x) = i + 1
Columns(i + 1).Hidden = False
x = x + 1
End If
Next

'your code

For i = LBound(ColumnsList) To UBound(ColumnsList)
Columns(ColumnsList(i)).Hidden = True
Next
End Sub

Why not make it non-version dependant?

x = 0
For i = 1 To ActiveSheet.Columns.Count
If Columns(i).Hidden Then
ReDim Preserve ColumnsList(x): ColumnsList(x) = i
Columns(i).Hidden = False: x = x + 1
End If
Next

then...

For i = LBound(ColumnsList) to UBound(ColumnsList)
Columns(ColumnsList(i + 1)).Hidden = True
Next

 

[Clif McIrvin]

isabelle explained :

hi Steve,

Sub Macro1()
Dim ColumnsList()
Dim x As Integer, i As Integer
x = 0
For i = 0 To 255 'or 16383  depend on ver.
If Columns(i + 1).Hidden = True Then
ReDim Preserve ColumnsList(x)
ColumnsList(x) = i + 1
Columns(i + 1).Hidden = False
x = x + 1
End If
Next

'your code

For i = LBound(ColumnsList) To UBound(ColumnsList)
Columns(ColumnsList(i)).Hidden = True
Next
End Sub

Why not make it non-version dependant?

x = 0
For i = 1 To ActiveSheet.Columns.Count
If Columns(i).Hidden Then
ReDim Preserve ColumnsList(x): ColumnsList(x) = i
Columns(i).Hidden = False: x = x + 1
End If
Next

then...

For i = LBound(ColumnsList) to UBound(ColumnsList)
Columns(ColumnsList(i + 1)).Hidden = True
Next

Typo Alert!!! ???

Shouldn't that last be

Columns(ColumnsList(i)).Hidden = True

??

 

[isabelle]

that's good !
thank's

 

[GS]

Clif McIrvin explained on 5/11/2011 :

Typo Alert!!! ???

Shouldn't that last be

Columns(ColumnsList(i)).Hidden = True

No, clif. In this case LBound=0 and there is no Columns(0) on a wks.

I'll admit I made a few really stupid, stupid mistakes today due to it
being an overload day. I did, however, clear that overload away and got
this one right! Whew...

 

[isabelle]

or, to reduce the number of loops

For i = 1 To ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Column

 

[Clif McIrvin]

Clif McIrvin explained on 5/11/2011 :

No, clif. In this case LBound=0 and there is no Columns(0) on a wks.

Sorry, I still disagree .... we're not referring to Columns(0); but to
the Columns(i) of the i that was originally placed into ColumnsList(x)
by the first loop.

 

[isabelle]

you're right Clif, Garry added +1 where it should not

please Garry check this line,
Columns(ColumnsList(i + 1)).Hidden = True

the good one is
Columns(ColumnsList(i)).Hidden = True

 

[Clif McIrvin]

I'll admit I made a few really stupid, stupid mistakes today due to it
being an overload day.

Been there.
Done that.

More often than I like to admit.

 

[GS]

Clif McIrvin has brought this to us :

Sorry, I still disagree .... we're not referring to Columns(0); but to the
Columns(i) of the i that was originally placed into ColumnsList(x) by the
first loop.

LBound here is x, which is 0. The value it holds is 1 because that's
where the loop started:

For i = 1 To ActiveSheet.Columns.Count

x was incremented to 1 for the next pass, i incremented to 2.

Or am I misunderstanding what you mean here?

 

[GS]

isabelle pretended :

or, to reduce the number of loops

For i = 1 To ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Column

Also...

For i = 1 to ActiveSheet.UsedRange.Columns.Count

 

[GS]

isabelle explained on 5/11/2011 :

you're right Clif, Garry added +1 where it should not

please Garry check this line,
Columns(ColumnsList(i + 1)).Hidden = True

the good one is
Columns(ColumnsList(i)).Hidden = True

Ok, lets walk through...

x = 0 'sets the index for the 1st element in ColumnsList

For i = 1 To ActiveSheet.Columns.Count 'start the counter at 1

If Columns(i).Hidden Then ' Columns(1).Hidden


ReDim Preserve ColumnsList(x) 'resizes to 0 (1 element)
ColumnsList(x) = i 'puts i (1) into Columns(0)

Columns(i).Hidden = False 'unhides Columns(1) if hidden
x = x + 1 'increments x to 1 for next redim for 2nd element

End If
Next

then...

For i = LBound(ColumnsList) to UBound(ColumnsList)
'LBound=0 because x started at 0
Columns(ColumnsList(i + 1)).Hidden = True
'0+1=1 where I went to school<g>
Next

 

[GS]

Duh..! Ok, thanks Clif, Isabelle. I just saw my error. Both of you are
correct. It should be...

Columns(ColumList(i)) because ColumnList(i)=1

I guess I'm still on overload, and so I'm going to get some sleep...

 

[isabelle]

have sweet dreams!

 

[GS]

zzzzzzzzzzzzzzzzzz...

 

[Charabeuh]

Hello Steve,

Perhaps another one :
- without index i => i or i+1 ?
- that might work if no row is hidden
- that might work if all rows are hidden
- that might work if some hidden columns are behind the last used
column.

Sub Macro2()
Dim rgHidden As Range, xCell As Range

For Each xCell In ActiveSheet.Rows(1).Cells
If xCell.EntireColumn.Hidden Then
If rgHidden Is Nothing Then
Set rgHidden = Columns(xCell.Column)
Else
Set rgHidden = Union(rgHidden, Columns(xCell.Column))
End If
End If
Next xCell
ActiveSheet.Columns.Hidden = False

' -------------------your code

If Not rgHidden Is Nothing Then rgHidden.Columns.Hidden = True
End Sub






Steve :

 

[Steve]

Thanks Garry, Isabelle and Clif!

loving the banter!!

One small problem - when I run the code, I'm getting an error:

x = 0
For i = 1 To wksSource.Columns.Count
If Columns(i).Hidden Then
ReDim Preserve ColumnsList(x): ColumnsList(x) = i
Columns(i).Hidden = False: x = x + 1
End If
Next

When I go into debug mode and hover over this line:
ReDim Preserve ColumnsList(x): ColumnsList(x) = i

It says subscript out of range.

Any thoughts?

 

[Steve]

I think I'm learning something here!! When I am in debug mode, if I
hover over:
If Columns(i).Hidden Then
It is telling me that i = 257...
from just reading the code, I thought it would start at 1 and
increment 1 until it reached 256. Apparently it is starting at 256+1!