Cannot set ColumnWidth past column 148
 

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

Cannot set ColumnWidth past column 148
 

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

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

Cannot set ColumnWidth past column 148
 

Gary,
Endcolumn = 198
Mik

--
MNTy
-----------------------------------------------------------------------
MNTye's Profile: http://www.excelforum.com/member.php...fo&userid=3605
View this thread: http://www.excelforum.com/showthread.php?threadid=55844

Cannot set ColumnWidth past column 148
 

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

--
Gary Brown




"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

Cannot set ColumnWidth past column 148
 

cols.address = $ES$6

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

--
MNTy
-----------------------------------------------------------------------
MNTye's Profile: http://www.excelforum.com/member.php...fo&userid=3605
View this thread: http://www.excelforum.com/showthread.php?threadid=55844

Cannot set ColumnWidth past column 148
 

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

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

Cannot set ColumnWidth past column 148
 

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

--
MNTy
-----------------------------------------------------------------------
MNTye's Profile: http://www.excelforum.com/member.php...fo&userid=3605
View this thread: http://www.excelforum.com/showthread.php?threadid=55844