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
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
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 NextJust string out all the headings you want to include.
Skip,
for a NUance!
RE: VB Code to change specific columns widths in a Table
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
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 iRE: VB Code to change specific columns widths in a Table
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
Skip,
Just traded in my OLD subtlety...
for a NUance!