VB Code to change specific columns widths in a Table 2

[Kenja824]

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.

 

[SkipVought]

Hi,

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

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Kenja824]

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.

 

[SkipVought]

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,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Kenja824]

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?

 

[cowski]

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.

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

 

[Kenja824]

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

 

[SkipVought]

Not a problem at all.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]