INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • 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.

Jobs

VB Code to change specific columns widths in a Table

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

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

'loop through all columns to add some padding
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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

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.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources


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:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close