Cannot set ColumnWidth past column 148

[MNTye]

I have a spreadsheet with 198 columns built by VBA procedures. Columns 7 thru 198 contain monthly data, i.e., columns 7 thru 18 contain data for the months of year 1, columns 19 thru 30 contain data for the months of year 2, ...., columns 187 thru 198 contain data for the months of year 16.

I need to set the ColumnWidth for columns 7 thru 198 to 4. I've never had a problem setting ColumnWidth before as I do this (among other things) for a living for the past 8 months. I've tried multiple methods, i.e., Range, Range-by-Range, Column-by-Column, and each one fails at column 149.

Please Login or Register  to view this content.

I appreciate any help you can give. Thanks!

[Gary Brown]

Don't know what the issue may be.
Have you debugged to see what the value of 'Endcolumn' is?
Debug.Print Endcolumn
--
HTH,
Gary Brown
[email protected]
If this post was helpful to you, please select ''YES'' at the bottom of the
post.



"MNTye" wrote:

>
> I have a spreadsheet with 198 columns built by VBA procedures. Columns 7
> thru 198 contain monthly data, i.e., columns 7 thru 18 contain data for
> the months of year 1, columns 19 thru 30 contain data for the months of
> year 2, ...., columns 187 thru 198 contain data for the months of year
> 16.
>
> I need to set the ColumnWidth for columns 7 thru 198 to 4. I've never
> had a problem setting ColumnWidth before as I do this (among other
> things) for a living for the past 8 months. I've tried multiple
> methods, i.e., Range, Range-by-Range, Column-by-Column, and each one
> fails at column 149.
>
>
> Code:
> --------------------
> Dim col As Integer
> .
> .
> .
> Startcolumn = Range(Replace(Replace(Replace(Replace(ActiveSheet.Name, ")", "_"), "(", "_"), " ", "_"), "-", "_") + "endheader").Column
> Endcolumn = Range(Replace(Replace(Replace(Replace(ActiveSheet.Name, ")", "_"), "(", "_"), " ", "_"), "-", "_") + "endheader2").Column
> col = Startcolumn
> While col < Endcolumn
> For Each cols In ActiveSheet.Range(Cells(6, col), Cells(6, col))
> cols.ColumnWidth = 4
> Next cols
> col = col + 1
> Wend
> --------------------
>
>
> I appreciate any help you can give. Thanks!
>
>
> --
> MNTye
> ------------------------------------------------------------------------
> MNTye's Profile: http://www.excelforum.com/member.php...o&userid=36058
> View this thread: http://www.excelforum.com/showthread...hreadid=558448
>
>

[MNTye]

Gary,
Endcolumn = 198
Mike

[Gary Brown]

I haven't been able to duplicate your issue.
What address is cols.address showing as it's final processed address?

--
Gary Brown
[email protected]



"MNTye" wrote:

>
> Gary,
> Endcolumn = 198
> Mike
>
>
> --
> MNTye
> ------------------------------------------------------------------------
> MNTye's Profile: http://www.excelforum.com/member.php...o&userid=36058
> View this thread: http://www.excelforum.com/showthread...hreadid=558448
>
>

[MNTye]

cols.address = $ES$6

Further testing has also shown that it also fails if I do not set the columnwidth for columns 7 through 148 and instead start the loop at column 149!?!

[Gary Brown]

This worked fine for me...
'-------------------------------------------------------------------------
Sub Macro1()
Dim col As Integer
Dim Startcolumn As Integer, Endcolumn As Integer

Startcolumn = 7
Endcolumn = 198
col = Startcolumn

For col = Startcolumn To Endcolumn - 1
ActiveSheet.Range(Cells(6, col), Cells(6, col)).ColumnWidth = 4
Next col

End Sub
'-------------------------------------------------------------------------

I'm wondering what your values for Startcolumn and Endcolumn are.
I can't think of any other issues that would cause this to fail.

--
HTH,
Gary Brown
[email protected]
If this post was helpful to you, please select ''YES'' at the bottom of the
post.



"MNTye" wrote:

>
> cols.address = $ES$6
>
> Further testing has also shown that it also fails if I do not set the
> columnwidth for columns 7 through 148 and instead start the loop at
> column 149!?!
>
>
> --
> MNTye
> ------------------------------------------------------------------------
> MNTye's Profile: http://www.excelforum.com/member.php...o&userid=36058
> View this thread: http://www.excelforum.com/showthread...hreadid=558448
>
>

[MNTye]

Gary,
You used the same values, 7 and 198. I've given up on fixing it this way. I found that if I default the entire sheet's columnwidths to 4 via "Cells.ColumnWidth = 4" then I only have to set the columnwidth for columns 1 through 6, which I do anyway via Autofit + 2, and everything is fine. I'd still like to know the answer but I've got to move on to other tasks. Thanks!
Mike