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!

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

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

hi Steve,


--
isabelle

isabelle explained :
hi Steve,

Code:

 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

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

"GS" wrote in message
...
isabelle explained :
hi Steve,

Code:

 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

??

--
Clif McIrvin

(clare reads his mail with moe, nomail feeds the bit bucket :-)

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

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

"GS" wrote in message
...
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.


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.

--
Clif McIrvin

(clare reads his mail with moe, nomail feeds the bit bucket :-)

"GS" wrote in message
...

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.

--
Clif McIrvin

(clare reads his mail with moe, nomail feeds the bit bucket :-)

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

--
isabelle

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

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

that's good !
thank's

--
isabelle


Le 2011-05-11 18:10, GS a écrit :
Why not make it non-version dependant?
For i = 1 To ActiveSheet.Columns.Count

or, to reduce the number of loops

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

--
isabelle

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

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

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 :
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!

Hello,

Another one shorter without any loop:
'-------------------------------------------------------------------
Sub macro3()
Dim rgVisible As Range, Mysheet As Worksheet

Set Mysheet = ActiveSheet
With Mysheet
On Error Resume Next
Set rgVisible = .Rows("1:1").SpecialCells(xlCellTypeVisible)
.Columns.Hidden = False
On Error GoTo 0
End With
'
'----------------Your code
'
With Mysheet
.Columns.Hidden = True
If Not rgVisible Is Nothing Then _
rgVisible.EntireColumn.Hidden = False
End With
End Sub
'-------------------------------------------------------------------




Steve a émis l'idée suivante :
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!

Just one line more because row(1:1) could be hidden too !
Try this:

'''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''''''''
Sub Test()
Dim rgVisible As Range, Mysheet As Worksheet

Set Mysheet = ActiveSheet
With Mysheet
On Error Resume Next
Set rgVisible = .Cells.SpecialCells(xlCellTypeVisible)
Set rgVisible = rgVisible.Cells(1, 1).EntireRow. _
SpecialCells(xlCellTypeVisible)
.Columns.Hidden = False
On Error GoTo 0
End With
'
'--------------------------------------Your code
'
With Mysheet
.Columns.Hidden = True
If Not rgVisible Is Nothing Then _
rgVisible.EntireColumn.Hidden = False
End With
Mysheet.Activate
ActiveWindow.ScrollColumn = 1: ActiveWindow.ScrollRow = 1
End Sub
'''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''''''''





Charabeuh a émis l'idée suivante :
Hello,

Another one shorter without any loop:
'-------------------------------------------------------------------
Sub macro3()
Dim rgVisible As Range, Mysheet As Worksheet

Set Mysheet = ActiveSheet
With Mysheet
On Error Resume Next
Set rgVisible = .Rows("1:1").SpecialCells(xlCellTypeVisible)
.Columns.Hidden = False
On Error GoTo 0
End With
'
'----------------Your code
'
With Mysheet
.Columns.Hidden = True
If Not rgVisible Is Nothing Then _
rgVisible.EntireColumn.Hidden = False
End With
End Sub
'-------------------------------------------------------------------