Hi all, i have an automated spread sheet that i need to change the
column widths to the following with code...

A=6, B=7 & C=1...

It must carry on with D=6, E=7 & F=1... doing the width of the data on
the spreadsheet...

Any help would be welcomed...

Best regards,

Les Stout

*** Sent via Developersdex http://www.developersdex.com ***

Here you go, place in the sheets code, change 100 to what column you want to
stop at:
Sub main()

For i = 1 To 100 Step 3
Columns(i).ColumnWidth = 6
Columns(i + 1).ColumnWidth = 7
Columns(i + 2).ColumnWidth = 1

Next
End Sub
--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"Les Stout" wrote:

> Hi all, i have an automated spread sheet that i need to change the
> column widths to the following with code...
>
> A=6, B=7 & C=1...
>
> It must carry on with D=6, E=7 & F=1... doing the width of the data on
> the spreadsheet...
>
> Any help would be welcomed...
>
> Best regards,
>
> Les Stout
>
> *** Sent via Developersdex http://www.developersdex.com ***
>

Les,

The following should work. It will set the column widths for the used range
across the sheet in sets of three. If the sheet is empty of data before
beginning, change the "ActiveSheet.UsedRange.Columns.Count" to the max
columns you want.

Option Explicit

Public Sub SetColWidths()

Dim i As Integer

For i = 1 To ActiveSheet.UsedRange.Columns.Count Step 3
Columns(i).ColumnWidth = 6
Columns(i + 1).ColumnWidth = 7
Columns(i + 2).ColumnWidth = 1
Next i


End Sub

"Les Stout" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Hi all, i have an automated spread sheet that i need to change the
> column widths to the following with code...
>
> A=6, B=7 & C=1...
>
> It must carry on with D=6, E=7 & F=1... doing the width of the data on
> the spreadsheet...
>
> Any help would be welcomed...
>
> Best regards,
>
> Les Stout
>
> *** Sent via Developersdex http://www.developersdex.com ***

This is what you need:

Sub setmywidth()

For i = 1 To 253
Cells(1, i).ColumnWidth = 6
Cells(1, i).Offset(0, 1).ColumnWidth = 7
Cells(1, i).Offset(0, 2).ColumnWidth = 1

i = i + 2
Next i

End Sub

Michael Arch.

Please click on the was this posting helpful if it was



"Les Stout" wrote:

> Hi all, i have an automated spread sheet that i need to change the
> column widths to the following with code...
>
> A=6, B=7 & C=1...
>
> It must carry on with D=6, E=7 & F=1... doing the width of the data on
> the spreadsheet...
>
> Any help would be welcomed...
>
> Best regards,
>
> Les Stout
>
> *** Sent via Developersdex http://www.developersdex.com ***
>

Ok, i have made the following, which is now working but my problem is
that the width(amount of Columns) can be variable, can one use this in
conjunction with a variable ???

Sub MakeThemFitBetter()
'
Dim vSizes As Variant, lCol As Long
Dim N As Long, ws As Worksheet
Set ws = ActiveSheet
lCol = ActiveSheet.UsedRange.Columns.Count
'one number for each column width
vSizes = Array(6, 7, 1, 6, 7, 1, 6, 7, 1)'<--Variable ??
For N = 1 To lCol
ws.Columns(N).ColumnWidth = vSizes(N - 1)
Next
End Sub


Best regards,

Les Stout

*** Sent via Developersdex http://www.developersdex.com ***

Thank you both so much, i have used the code you supplied... :0) working
100% thank you again

Best regards,

Les Stout

*** Sent via Developersdex http://www.developersdex.com ***

Your method works fine but will throw an error if the number of columns is
ever more than 9 becasue vSizes = Array(6, 7, 1, 6, 7, 1, 6, 7, 1) puts only
9 numbers in your array if n is ever more than 9 it will crash. To work
around what will be the resoning behind why a certain column is a certain
size?
--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"Les Stout" wrote:

> Ok, i have made the following, which is now working but my problem is
> that the width(amount of Columns) can be variable, can one use this in
> conjunction with a variable ???
>
> Sub MakeThemFitBetter()
> '
> Dim vSizes As Variant, lCol As Long
> Dim N As Long, ws As Worksheet
> Set ws = ActiveSheet
> lCol = ActiveSheet.UsedRange.Columns.Count
> 'one number for each column width
> vSizes = Array(6, 7, 1, 6, 7, 1, 6, 7, 1)'<--Variable ??
> For N = 1 To lCol
> ws.Columns(N).ColumnWidth = vSizes(N - 1)
> Next
> End Sub
>
>
> Best regards,
>
> Les Stout
>
> *** Sent via Developersdex http://www.developersdex.com ***
>

Found that, that is why i went with your code, thanks John..... Much
appreciated.

Best regards,

Les Stout

*** Sent via Developersdex http://www.developersdex.com ***

here is a workaround for you to try:

Sub MakeThemFitBetter()
Dim vSizes As Variant, lCol As Long
Dim N As Long, ws As Worksheet
Dim X As Long
Set ws = ActiveSheet
lCol = ActiveSheet.UsedRange.Columns.Count
'one number for each column width
vSizes = Array(6, 7, 1) '<--Variable ??

For X = 1 To lCol
ws.Columns(X).ColumnWidth = vSizes(N)
N = N + 1
If N = 3 Then N = 0
Next
End Sub

--


Gary


"Les Stout" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> Found that, that is why i went with your code, thanks John..... Much
> appreciated.
>
> Best regards,
>
> Les Stout
>
> *** Sent via Developersdex http://www.developersdex.com ***

Thanks Gary, i am ok with this now... Please could you help me with my
other thread.... Pulling my hair out !!! (Help to modify code)

Best regards,

Les Stout

*** Sent via Developersdex http://www.developersdex.com ***