CODEWITHSUNDEEP
vbaexcelfor-looprange
beks123
beks123

Reputation: 55

Using for loop to set ranges of column widths in VBA

enter image description here

I want to be able to use some sort of for loop to set column widths in an excel sheet so I don't have to type out each column I want to change the width of. In the case of this picture, I want all black columns to have a width of 0.92, green to be 0.83, and orange to be 7.29, and I want these to continue past what I've shown here for a set range. Below is the general code I used, but like I said, I don't want to have to type out every column to change the width.

sub Set_Column_Width

   Range("E:E, I:I, M:M, Q:Q, U:U, Y:Y, AC:AC, AG:AG, AK:AK, AO:AO, AS:AS, AT:AT, AX:AX").ColumnWidth = 0.92
  Range("G:G,K:K,O:O,S:S").ColumnWidth = 0.83
   Range("F:F, H:H, J:J, L:L").ColumnWidth = 7.29

End sub

Upvotes: 0

Views: 3042

Answers (3)

Matt Cremeens
Matt Cremeens

Reputation: 5151

While I'm not sure what those colors are, it would be easy enough to find out. If the entire column is the same color, you could just loop through the columns and look at row 1. Something like

for i = 1 to 1000 'or whatever your last column is
    if cells(1, i).interior.color = rgb(255, 0, 0) then 'red, for example
        cells(1, i).columnwidth = 0.83 'or whatever the column width needs to be here
    else if cells(1, i).interior.color = rgb(...) then 'next color
        cells(1, i).columnwidth = ... 'etc.
    end if
next i

Upvotes: 1

David Glickman
David Glickman

Reputation: 790

If you are doing a pattern, black-orange-green-orange you can loop so.

for i = [first column] to [last column] step 4
     Columns(i).ColumnWidth = 0.92
     Columns(i+1).ColumnWidth = 7.29
     Columns(i+2).ColumnWidth = 0.83
     Columns(i+3).ColumnWidth = 7.29
next i

Upvotes: 2

arcadeprecinct
arcadeprecinct

Reputation: 3777

Use Columns(index)

Dim i As Long

For i = 5 To 105 Step 4
    Columns(i).Columwidth = 0.92
next i

For i = 6 To 106 Step 2
    Columns(i).Columwidth = 7.29
next i

For i = 7 To 107 Step 4
    Columns(i).Columwidth = 0.83
next i

The other way would be to determine the width based on the index

For i = 5 To 100
    'find you what width this column should have
    Columns(i).Columnwidth = x
next i

This is assuming the widths are not defined by the colors but by their position

Upvotes: 1

Related Questions