Change every 3 columns width with code

[Les Stout]

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

 

[John Bundy]

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

 

[Dove]

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

 

[Michael]

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]

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

 

[Les Stout]

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

 

[John Bundy]

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?

 

[Les Stout]

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

 

[Gary Keramidas]

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

 

[Les Stout]

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

 

[Gary Keramidas]

looks like you solved it.

 

[Les Stout]

No Gary, it is a new thread subject" Help to modify code"

Best regards,

Les Stout

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