PDA

View Full Version : Solved: quick question formatting column widths



TrippyTom
10-24-2008, 06:29 PM
This is my code:
Columns("P:P").ColumnWidth = 7.86
Columns("R:R").ColumnWidth = 7.86
Columns("T:T").ColumnWidth = 7.86
Columns("V:V").ColumnWidth = 7.86
Columns("X:X").ColumnWidth = 7.86
Columns("Z:Z").ColumnWidth = 7.86

Is there a shorter version of doing this to multiple columns that are not contiguous?

Krishna Kumar
10-24-2008, 07:10 PM
May be

Dim Cols
Cols = Array(16, 18, 20, 22, 24, 26) 'column numbers
For c = 0 To UBound(Cols)
Columns(Cols(c)).ColumnWidth = 7.86
Next

GTO
10-24-2008, 09:09 PM
Greetings Tom,

Range and Union can be used to do such things in one swipe.

Hope this helps,

Mark

Sub AdjustColWid()

Range("P:P,R:R,T:T,V:V,X:X,Z:Z").ColumnWidth = 7.86

End Sub

mdmackillop
10-25-2008, 05:10 AM
For regular spacings you can use Mod to identify rows/columns

Sub CWidth()
For i = 16 To 26
If i Mod 2 = 0 Then Columns(i).ColumnWidth = 2
If i Mod 2 = 1 Then Columns(i).ColumnWidth = 20
Next
End Sub

Andy Pope
10-25-2008, 06:07 AM
this combines all the suggested approaches


Dim rngCol As Range
For Each rngCol In Columns("P:Z")
If rngCol.Column Mod 2 = 0 Then rngCol.ColumnWidth = 7.86
Next

GTO
10-25-2008, 06:14 AM
@Malcom:

Showoff! (Okay, seriously - thank you. WAY more 'adjustable' and I would not have ever thought of that.)

@Trippy:

Bright idea for signature!

Ya'll have a terrific weekend!

Mark

GTO
10-25-2008, 06:17 AM
OOPS! Sorry Andy. My typing * laptop speed = late...

Mark

TrippyTom
10-27-2008, 08:32 PM
Thanks to all! I knew someone would jump on this very easy question. :)

Happy Halloween!