Identify Hidden Columns

[Clif McIrvin]

Comments inline ...

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>

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

 

[isabelle]

hi,

if you use Columns.Count
try with

For i = 1 To .Columns.Count -1

 

[Steve]

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.

Comments inline ...


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>

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]

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.

 

[Steve]

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.

Comments inline ...


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>

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]

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.

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

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

Comments inline ...


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>

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]

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

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.

Comments inline ...


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>

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

 

[Steve]

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

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


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.

Comments inline ...

"Steve" <[email protected]> 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>

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

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

 

[isabelle]

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

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

 

[isabelle]

rectification

For i = 0 To. Columns.Count -1

 

[Clif McIrvin]

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

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

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


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.

Comments inline ...

"Steve" <[email protected]> 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>

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

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

 

[GS]

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

 

[Clif McIrvin]

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.

 

[Charabeuh]

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]

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

 

[Clif McIrvin]

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.

 

[Steve]

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

 

[Charabeuh]

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