Programatically adjust col width excel 2003 1

[sxschech]

Anyone have some code that can programatically adjust the col width. Data are imported each week and number of cols may change, hoping that this can be done without user intervention to loop through each col and based on the char length of the "header" would set the col width accordingly. Example:

len(F2) is 7 then width = 10

Perhaps rather than specific lengths was thinking length range

If len("COL")<09 then width = 9
If len("COL")=09 then width = 10
If len("COL")>09 then width = 12

For this particular case, the width adjustments would begin at Col E until last col of data is encountered. This week's file ends at Col DM. next week could end before or after that.

Thanks.

 

[CautionMP]

sxschech,
This is from Excel 2k but I imagine it will work in 2003.

Sub ResizeColumns()
Dim MyRange As Range, MyColumn As Range
Set MyRange = ActiveSheet.UsedRange
For Each MyColumn In MyRange.Columns
  'You could replace with the logic in your post
  'if you don't want to use the AutoFit
  MyColumn.AutoFit
Next MyColumn
End Sub

Hope this helps,
CMP

(GMT-07:00) Mountain Time (US & Canada)

 

[sxschech]

Thanks for the quick response, unfortunately, I'm not as familiar working with Excel VBA as in Access VBA. Need a bit more information on how to refer to the cells in row 2 to determine the length and also how to start the width code at Col E rather than Col A.

 

[CautionMP]

How about this, be sure to update [tt]sheet2[/tt] to match your actual sheet name.

Sub ResizeBasedOnRow2()
Dim wksToResize As Worksheet
Dim rngColumn As Range
Dim lngColumn As Long, lngLastColumn As Long

'Point to the sheet we're working with
Set wksToResize = Worksheets("[b]sheet2[/b]")

'Get the last used column
lngLastColumn = wksToResize.UsedRange.Columns.Count

'5 is column E, move from E to last column used
For lngColumn = 5 To lngLastColumn
  'Get the current column
  Set rngColumn = wksToResize.Columns(lngColumn)
  'Check the text length and resize accrodingly
  Select Case Len(wksToResize.Cells(2, lngColumn).Value)
    Case Is < 9
      rngColumn.ColumnWidth = 9
    Case Is = 9
      rngColumn.ColumnWidth = 10
    Case Else
      rngColumn.ColumnWidth = 12
  End Select
Next lngColumn

CleanUp:
Set rngColumn = Nothing
Set wksToResize = Nothing
End Sub

CMP


(GMT-07:00) Mountain Time (US & Canada)

 

[sxschech]

That is great. Thanks.