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

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

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

or, to reduce the number of loops

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

--
isabelle

"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 :-)

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

"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 :-)

Clif McIrvin has brought this to us :
"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.

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?

--
Garry

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

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

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

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

--
Garry

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

have sweet dreams!

--
isabelle

zzzzzzzzzzzzzzzzzz...

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

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?

On May 11, 8:35*pm, GS wrote:
zzzzzzzzzzzzzzzzzz...

--
Garry

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

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!


On May 12, 9:14*am, Steve wrote:
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?

On May 11, 8:35*pm, GS wrote:



zzzzzzzzzzzzzzzzzz...

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc- Hide quoted text -

- Show quoted text -

Comments inline ...

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

<c No -- it's starting at column 1 ... at least if you're using the
code we've seen posted.
i=257 simply says that you've already iterated the first 256 columns of
your worksheet.
</c


On May 12, 9:14 am, Steve wrote:
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

Note that [ Columns(i).Hidden ] could be pointing to a different
worksheet than
[ wksSource.Columns.Count ] is. Without the object qualifier, Columns
defaults to the active worksheet; which may not be the sheet you want.

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.

Which line is throwing the error? That is, when you enter debug mode,
which line is highlighted in yellow? Is the subscript out of range the i
or the x ???


Try changing that loop like this:

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

so that every reference to Columns uses the wksSource object qualifier
(note the [ . ] in front of each .Columns).


--
Clif McIrvin

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

hi,

if you use Columns.Count
try with

For i = 1 To .Columns.Count -1


--
isabelle

Le 2011-05-12 12:16, Steve a écrit :
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!


On May 12, 9:14 am, wrote:
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?

On May 11, 8:35 pm, wrote:



zzzzzzzzzzzzzzzzzz...

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc- Hide quoted text -

- Show quoted text -

Hi Clif,

The line that is throwing the error is:
For i = LBound(ColumnsList) To UBound(ColumnsList)

When I hover over LBound and UBound I see the subscript oupt of range
error.


On May 12, 11:27*am, "Clif McIrvin" wrote:
Comments inline ...

"Steve" wrote in message

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

<c No -- it's starting at column 1 ... at least if you're using the
code we've seen posted.
i=257 simply says that you've already iterated the first 256 columns of
your worksheet.
</c

On May 12, 9:14 am, Steve wrote:

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

Note that [ Columns(i).Hidden ] could be pointing to a different
worksheet than
[ wksSource.Columns.Count ] is. *Without the object qualifier, Columns
defaults to the active worksheet; which may not be the sheet you want.

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.

Which line is throwing the error? *That is, when you enter debug mode,
which line is highlighted in yellow? Is the subscript out of range the i
or the x ???

Try changing that loop like this:

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

so that every reference to Columns uses the wksSource object qualifier
(note the [ . ] in front of each .Columns).

--
Clif McIrvin

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

"isabelle" wrote in message
...
hi,

if you use Columns.Count
try with

For i = 1 To .Columns.Count -1

???

..Columns.Count should be the number of the last column.

--
Clif



--
isabelle

Le 2011-05-12 12:16, Steve a écrit :
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!


On May 12, 9:14 am, wrote:
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?

On May 11, 8:35 pm, wrote:



zzzzzzzzzzzzzzzzzz...

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc- Hide quoted
text -

- Show quoted text -




--
Clif McIrvin

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

Clif,

The new code with the With statement worked! Thank you!

As I was testting, I ran across one small issue - the code errors out
if ZERO columns are hidden on wksSource.


On May 12, 11:27*am, "Clif McIrvin" wrote:
Comments inline ...

"Steve" wrote in message

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

<c No -- it's starting at column 1 ... at least if you're using the
code we've seen posted.
i=257 simply says that you've already iterated the first 256 columns of
your worksheet.
</c

On May 12, 9:14 am, Steve wrote:

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

Note that [ Columns(i).Hidden ] could be pointing to a different
worksheet than
[ wksSource.Columns.Count ] is. *Without the object qualifier, Columns
defaults to the active worksheet; which may not be the sheet you want.

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.

Which line is throwing the error? *That is, when you enter debug mode,
which line is highlighted in yellow? Is the subscript out of range the i
or the x ???

Try changing that loop like this:

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

so that every reference to Columns uses the wksSource object qualifier
(note the [ . ] in front of each .Columns).

--
Clif McIrvin

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

"Steve" wrote in message
...
Hi Clif,

The line that is throwing the error is:
For i = LBound(ColumnsList) To UBound(ColumnsList)

When I hover over LBound and UBound I see the subscript oupt of range
error.

<cAh! Now I see what Charabeuh meant about an error if there are no
hidden columns. If you investigate, I expect you will find that there
are no hidden columns, and ColumnsList is an undefined array (ie, UBound
is less than LBound ... or something like that.)

Did you see Charabeuh's post? It begins:

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

It shows as 5/11 10:37 PM in my newsreader.

You may find the suggestions in that post quite helpful.</c

--
Clif

On May 12, 11:27 am, "Clif McIrvin" wrote:
Comments inline ...

"Steve" wrote in message

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

<c No -- it's starting at column 1 ... at least if you're using the
code we've seen posted.
i=257 simply says that you've already iterated the first 256 columns
of
your worksheet.
</c

On May 12, 9:14 am, Steve wrote:

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

Note that [ Columns(i).Hidden ] could be pointing to a different
worksheet than
[ wksSource.Columns.Count ] is. Without the object qualifier, Columns
defaults to the active worksheet; which may not be the sheet you want.

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.

Which line is throwing the error? That is, when you enter debug mode,
which line is highlighted in yellow? Is the subscript out of range the
i
or the x ???

Try changing that loop like this:

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

so that every reference to Columns uses the wksSource object qualifier
(note the [ . ] in front of each .Columns).

--
Clif McIrvin

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




--
Clif McIrvin

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

Did you also modify the final loop?

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

No hidden columns was addressed in my last reply.

--
Clif

"Steve" wrote in message
...
Clif,

The new code with the With statement worked! Thank you!

As I was testting, I ran across one small issue - the code errors out
if ZERO columns are hidden on wksSource.


On May 12, 11:27 am, "Clif McIrvin" wrote:
Comments inline ...

"Steve" wrote in message

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

<c No -- it's starting at column 1 ... at least if you're using the
code we've seen posted.
i=257 simply says that you've already iterated the first 256 columns
of
your worksheet.
</c

On May 12, 9:14 am, Steve wrote:

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

Note that [ Columns(i).Hidden ] could be pointing to a different
worksheet than
[ wksSource.Columns.Count ] is. Without the object qualifier, Columns
defaults to the active worksheet; which may not be the sheet you want.

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.

Which line is throwing the error? That is, when you enter debug mode,
which line is highlighted in yellow? Is the subscript out of range the
i
or the x ???

Try changing that loop like this:

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

so that every reference to Columns uses the wksSource object qualifier
(note the [ . ] in front of each .Columns).

--
Clif McIrvin

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




--
Clif McIrvin

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

No, I forgot to modify the final loop. Thanks!


On May 12, 12:28*pm, "Clif McIrvin" wrote:
Did you also modify the final loop?

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

No hidden columns was addressed in my last reply.

--
Clif

"Steve" wrote in message

...
Clif,

The new code with the With statement worked! *Thank you!

As I was testting, I ran across one small issue - the code errors out
if ZERO columns are hidden on wksSource.

On May 12, 11:27 am, "Clif McIrvin" wrote:





Comments inline ...

"Steve" wrote in message

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

<c No -- it's starting at column 1 ... at least if you're using the
code we've seen posted.
i=257 simply says that you've already iterated the first 256 columns
of
your worksheet.
</c

On May 12, 9:14 am, Steve wrote:

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

Note that [ Columns(i).Hidden ] could be pointing to a different
worksheet than
[ wksSource.Columns.Count ] is. Without the object qualifier, Columns
defaults to the active worksheet; which may not be the sheet you want.

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.

Which line is throwing the error? That is, when you enter debug mode,
which line is highlighted in yellow? Is the subscript out of range the
i
or the x ???

Try changing that loop like this:

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

so that every reference to Columns uses the wksSource object qualifier
(note the [ . ] in front of each .Columns).

--
Clif McIrvin

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

--
Clif McIrvin

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

- Show quoted text -

i'm referring to the first macro that i submitted
this is why i say:
if you use Columns.Count
try
For i = 1 To. Columns.Count -1
because loop begin at 0



--
isabelle

Le 2011-05-12 14:10, Clif McIrvin a écrit :

.Columns.Count should be the number of the last column.

rectification

For i = 0 To. Columns.Count -1

--
isabelle

Le 2011-05-12 15:15, isabelle a écrit :
i'm referring to the first macro that i submitted
this is why i say:
if you use Columns.Count
try
For i = 1 To. Columns.Count -1
because loop begin at 0

Code:

 Sub Macro1()
 Dim ColumnsList()
 Dim x As Integer, i As Integer
 x = 0
 For i = 0 To 255 'or 16383
 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

I see I didn't pay close attention to what I pasted .... I hope you
didn't keep that extra +1 that crept back in there.

wksSource.Columns(ColumnsList(i)).Hidden = True

--
Clif

"Steve" wrote in message
...
No, I forgot to modify the final loop. Thanks!


On May 12, 12:28 pm, "Clif McIrvin" wrote:
Did you also modify the final loop?

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

No hidden columns was addressed in my last reply.

--
Clif

"Steve" wrote in message

...
Clif,

The new code with the With statement worked! Thank you!

As I was testting, I ran across one small issue - the code errors out
if ZERO columns are hidden on wksSource.

On May 12, 11:27 am, "Clif McIrvin" wrote:





Comments inline ...

"Steve" wrote in message

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

<c No -- it's starting at column 1 ... at least if you're using the
code we've seen posted.
i=257 simply says that you've already iterated the first 256 columns
of
your worksheet.
</c

On May 12, 9:14 am, Steve wrote:

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

Note that [ Columns(i).Hidden ] could be pointing to a different
worksheet than
[ wksSource.Columns.Count ] is. Without the object qualifier,
Columns
defaults to the active worksheet; which may not be the sheet you
want.

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.

Which line is throwing the error? That is, when you enter debug
mode,
which line is highlighted in yellow? Is the subscript out of range
the
i
or the x ???

Try changing that loop like this:

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

so that every reference to Columns uses the wksSource object
qualifier
(note the [ . ] in front of each .Columns).

--
Clif McIrvin

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

--
Clif McIrvin

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

- Show quoted text -




--
Clif McIrvin

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

I thought it was resolved to make this non-version dependant,
AND start looping the columns '1 To Columns.Count' so '+ 1' wasn't
required anymore. So...

Sub Macro1()
Dim ColumnsList()
Dim x As Integer, i As Integer
x = 0
For i = 1 To Columns.Count
If Columns(i).Hidden = True Then
ReDim Preserve ColumnsList(x)
ColumnsList(x) = i
Columns(i).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

--
Garry

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

"GS" wrote in message
...
I thought it was resolved to make this non-version dependant,
AND start looping the columns '1 To Columns.Count' so '+ 1' wasn't
required anymore. So...

I'm not sure; but I think the OP did go that direction. The subscript
out of range error I believe had to do with referencing multiple sheet
objects, which has been resolved by adding object qualification to the
routines.

Sounded like the OP was getting close to resolution.

--
Clif


Sub Macro1()
Dim ColumnsList()
Dim x As Integer, i As Integer
x = 0
For i = 1 To Columns.Count
If Columns(i).Hidden = True Then
ReDim Preserve ColumnsList(x)
ColumnsList(x) = i
Columns(i).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

--
Garry

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





--
Clif McIrvin

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

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!

Hey Gang,

Here is what I ended up with:

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

Do Stuff code.....

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

Look about right? Now I'm toying with the error handling for a
wksSource sheet that does not have any columns hidden prior to code
execution.

Thank you all for the help you provided!!!!

-Steve

On May 12, 2:08*pm, "Clif McIrvin" wrote:
"GS" wrote in message

...

I thought it was resolved to make this non-version dependant,
AND start looping the columns '1 To Columns.Count' so '+ 1' wasn't
required anymore. So...

I'm not sure; but I think the OP did go that direction. *The subscript
out of range error I believe had to do with referencing multiple sheet
objects, which has been resolved by adding object qualification to the
routines.

Sounded like the OP was getting close to resolution.

--
Clif







Sub Macro1()
Dim ColumnsList()
Dim x As Integer, i As Integer
x = 0
*For i = 1 To Columns.Count
* *If Columns(i).Hidden = True Then
* * *ReDim Preserve ColumnsList(x)
* * *ColumnsList(x) = i
* * *Columns(i).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

--
Garry

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

--
Clif McIrvin

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

- Show quoted text -

"Steve" wrote in message
...
Hey Gang,

Here is what I ended up with:

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

Do Stuff code.....

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

Look about right? Now I'm toying with the error handling for a
wksSource sheet that does not have any columns hidden prior to code
execution.

Thank you all for the help you provided!!!!

-Steve


Steve -- make sure to find and ponder Charabeuh's second reply to your
OP ... dated 5/12 3:58 pm on my newsreader. That is a very
straight-forward solution which resolves the problem of no hidden
columns.

--
Clif McIrvin

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

I didn't notice it...I'll check. Thanks!

On May 12, 3:23*pm, "Clif McIrvin" wrote:
"Steve" wrote in message

...
Hey Gang,

Here is what I ended up with:

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

Do Stuff code.....

* For i = LBound(ColumnsList) To UBound(ColumnsList)
* * wksSource.Columns(ColumnsList(i)).Hidden = True
* Next

Look about right? *Now I'm toying with the error handling for a
wksSource sheet that does not have any columns hidden prior to code
execution.

Thank you all for the help you provided!!!!

-Steve

Steve -- make sure to find and ponder Charabeuh's second reply to your
OP ... dated 5/12 3:58 pm on my newsreader. That is a very
straight-forward solution which resolves the problem of no hidden
columns.

--
Clif McIrvin

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

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