Solved

Excel - Increse columns in range by 5

Posted on 2016-11-21
6
21 Views
Last Modified: 2016-11-21
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
0
Comment
Question by:chris pike
  • 3
  • 2
6 Comments
 
LVL 31

Accepted Solution

by:
Rob Henson earned 500 total points
ID: 41896193
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
 
LVL 48

Expert Comment

by:Rgonzo1971
ID: 41896213
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
 
LVL 31

Expert Comment

by:Rob Henson
ID: 41896229
@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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 

Author Closing Comment

by:chris pike
ID: 41896262
Thanks,
That was an easy one.
0
 

Author Comment

by:chris pike
ID: 41896268
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
 

Author Comment

by:chris pike
ID: 41896269
I do have a related question to this question.
Posting now
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now