Excel - Increse columns in range by 5

Hi Experts,
I need VBA to increase all COLUMNS in a selected range by  +3

Range("A:A,B:B,C:C,D:D,E:E,F:F,G:G,H:H,O:O,R:R,U:U").Select

I tried taking the sub for increasing rows, which works, and changed it to columns, but it does not work. It increases ALL columns in the sheet  not the desired columns.

Here is what I tried...................
Sub TEST()
' test

Dim column As Range
    For Each column In Range("A:A,B:B,C:C,D:D,E:E,F:F,G:G,H:H,O:O,R:R,U:U").Columns
        Columns.ColumnWidth = column.ColumnWidth + 5
    Next column
End Sub

Thanks so much
Chris
chris pikeAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Rob HensonConnect With a Mentor Finance AnalystCommented:
Try this:

Sub Increase_Column_Width()
    
For C1 = 1 To 6
    CW = Cells(1, C1).ColumnWidth
    Cells(1, C1).ColumnWidth = CW + 5
Next C1

For C2 = 15 To 21 Step 3
    CW = Cells(1, C2).ColumnWidth
    Cells(1, C2).ColumnWidth = CW + 5
Next C2

End Sub

Open in new window

0
 
Rgonzo1971Commented:
Hi,

pls try
Sub TEST()


 Dim col As Range
     For Each col In Range("A:A,B:B,C:C,D:D,E:E,F:F,G:G,H:H,O:O,R:R,U:U").Columns
         col.ColumnWidth = col.ColumnWidth + 5
     Next col
 End Sub

Open in new window

regards
0
 
Rob HensonFinance AnalystCommented:
@Rgonzo - is the error in Chris's script because column is already a defined word so can't be used as a variable?

If not maybe the error is in the line:

Columns.ColumnWidth = column.ColumnWidth + 5

should be:

column.ColumnWidth = column.ColumnWidth + 5

note removal of "s" from column in first part.
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
chris pikeAuthor Commented:
Thanks,
That was an easy one.
0
 
chris pikeAuthor Commented:
Rgonzo1971
Sorry I didn't even see your comment when I awarded Rob
I didn't refresh screen , so I didn't see any additional comments.

But thanks for helping.
0
 
chris pikeAuthor Commented:
I do have a related question to this question.
Posting now
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.