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