×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Are you an
Engineering professional?
Join Eng-Tips Forums!
• Talk With Other Members
• Be Notified Of Responses
• Keyword Search
Favorite Forums
• Automated Signatures
• Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

#### Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

# VB Code to change specific columns widths in a Table2

## VB Code to change specific columns widths in a Table

(OP)

I am trying to use VB Code to change the width to columns with specific Headers (in a table) to select which columns to change. My code already does an auto-fit for all columns, but there are a few columns I need to ignore the header width and shrink it to the width of the info in the cells.

The Header would be "Guage 1" for instance. All of the cells will only have....
0.60
1.20
1.50
0.85

In other columns I have a header "Matl 1"
But the info in the cells is no more than two letters
XG
XB
G
B
VG
etc....

I dont want a lot of space between the info for these two types of columns so I shrink them down to look like they belong together.

### RE: VB Code to change specific columns widths in a Table

Hi,

Put "i" in the column header, AutoFit the column, capture/store the Column Width, return the original heading, assign column width.

Skip,

Just traded in my OLD subtlety...
for a NUance!

### RE: VB Code to change specific columns widths in a Table

(OP)
This is something that will be done with every new file. I was hoping to just have the VB code automatically change 6 specific columns to around 4.8 width. I cant find anywhere on line how to do the code for this using the headers to specify which columns.

### RE: VB Code to change specific columns widths in a Table

#### CODE

Dim c As Range

For Each c In Range(Cells(1,1), Cells(1,1).End(xlToRight))
Select Case c.Value
Case "Gauge 1","Gauge 2",".....
C.EntireColumn.ColumnWidth = 4.8
Case Else
C.EntireColumn.AutoFit
End Select
Next 

Just string out all the headings you want to include.

Skip,

Just traded in my OLD subtlety...
for a NUance!

### RE: VB Code to change specific columns widths in a Table

(OP)
Cool, that worked good. When I used it, though it worked right, I found I wanted the cells just a little larger than autowidth gives. A little more padding. I found a video on doing that and I adjusted the code to this....

Dim c As Range

For Each c In Range(Cells(1,1), Cells(1,1).End(xlToRight))
Select Case c.Value
Case "Gauge 1","Gauge 2","Gauge 3","Matl 1","Matl 2","Matl 3",".....
C.EntireColumn.ColumnWidth = 4.8
Case "SPOT #",".....
C.EntireColumn.ColumnWidth = 11.0
Case Else
C.EntireColumn.AutoFit
For i = 1 To ActiveSheet.UsedRange.Columns.Count
Columns(i).ColumnWidth = Columns (i).ColumnWidth + .1
Next i
End Select
Next

However, this makes EVERYTHING autofit with padding again. Could you show me how to adjust it so it only affects the cells I want to be autofit and not the cells I gave the 4.8 or 11 column width to?

### RE: VB Code to change specific columns widths in a Table

You've nested your padding "for" loop inside of your main "for" loop. This means that each time the main loop processes a single column, the padding loop will activate and add 0.1 to the width of every column; then your main loop takes over to process the next column and sets its width to 4.8, 11.0, or auto - then the padding loop fires again and adds 0.1 to every column etc etc.

Based on your description, this isn't what you want to do. I'd suggest moving the "padding" loop outside of the main loop so that it only process the columns one time, after the main resizing loop is done.

#### CODE

Dim c As Range

'main column resizing loop
For Each c In Range(Cells(1,1), Cells(1,1).End(xlToRight))
Select Case c.Value
Case "Gauge 1","Gauge 2","Gauge 3","Matl 1","Matl 2","Matl 3",".....
C.EntireColumn.ColumnWidth = 4.8
Case "SPOT #",".....
C.EntireColumn.ColumnWidth = 11.0
Case Else
C.EntireColumn.AutoFit
End Select
Next

For i = 1 To ActiveSheet.UsedRange.Columns.Count
Columns(i).ColumnWidth = Columns (i).ColumnWidth + .1
Next i 

### RE: VB Code to change specific columns widths in a Table

(OP)
Awesome. Thanks.

Looks like I am down to one more thing I need to figure out how to do for this macro. lol We will see if I can figure it out without begging you guys for it again. lol

### RE: VB Code to change specific columns widths in a Table

Not a problem at all.

Skip,

Just traded in my OLD subtlety...
for a NUance!

#### Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

#### Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Close Box

# Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

• Talk To Other Members
• Notification Of Responses To Questions
• Favorite Forums One Click Access
• Keyword Search Of All Posts, And More...

Register now while it's still free!