I have a worksheet that contains 268 columns with headers. I have a couple
of different ways I want to sort the data left to right by columns. The sort
itself works fine -- the issue is that I can't seem to maintain the integrity
of the original column width for each column of data. For example, let's say
the 'original' column width is as follows:

A = 10 B = 15 C = 5

If I sort the data so that columns B & C become reversed, the data that
'was' in column C now has a width of 15 (too large), while the data from
column B now has a width of 5 (too small). I cannot use autofit afterwards
because the wrapped headers in each column cause the widths to become even
more distorted. I am using VB code to perform the sort function--here is an
example of one of the sorts. Is there something I can do to maintain the
integrity of the column width so it stays with the original data?

Sub Sort_by_Measure()
'Sort columns left to right by Measure, then by Quarter
Application.ScreenUpdating = False
UnhideCols
Columns("E:JZ").Select
ActiveWorkbook.Worksheets("Detail").Sort.SortField s.Clear
ActiveWorkbook.Worksheets("Detail").Sort.SortField s.add
Key:=Range("E6:JZ6") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Detail").Sort.SortField s.add
Key:=Range("E5:JZ5") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Detail").Sort
.SetRange Range("E1:JZ20000")
.Header = xlNo
.MatchCase = False
.Orientation = xlLeftToRight
.SortMethod = xlPinYin
.Apply
End With
Hide_Detail_Cols
Range("A11").Select
Application.ScreenUpdating = True
End Sub

I stored the columns widths in an array before the sort and put the column
number in row 65536 before the sort. then restored the column widths after
the sort


Sub Sort_by_Measure()
Dim ColArray(256)

'Sort columns left to right by Measure, then by Quarter
Application.ScreenUpdating = False
UnhideCols

With ActiveWorkbook.Worksheets("Detail")
For ColCount = 0 To 255
'put width in array
ColArray(ColCount) = .Columns(ColCount + 1).Width
'put index in row 65536
.Cells(Rows.Count, ColCount) = ColCount
Next ColCount



.Columns("E:JZ").Sort _
header:=xlNo, _
Orientation:=xlSortColumns, _
key1:=.Range("E5"), _
order1:=xlAscending, _
key2:=.Range("E6"), _
order2:=xlAscending


For ColCount = 0 To 255
'put width in array
.Columns(ColCount + 1).Width = ColArray(ColCount)
Next ColCount
.Rows(Rows.Count).Delete

Hide_Detail_Cols

Application.ScreenUpdating = True
End Sub

"jday" wrote:

I have a worksheet that contains 268 columns with headers. I have a couple
of different ways I want to sort the data left to right by columns. The sort
itself works fine -- the issue is that I can't seem to maintain the integrity
of the original column width for each column of data. For example, let's say
the 'original' column width is as follows:

A = 10 B = 15 C = 5

If I sort the data so that columns B & C become reversed, the data that
'was' in column C now has a width of 15 (too large), while the data from
column B now has a width of 5 (too small). I cannot use autofit afterwards
because the wrapped headers in each column cause the widths to become even
more distorted. I am using VB code to perform the sort function--here is an
example of one of the sorts. Is there something I can do to maintain the
integrity of the column width so it stays with the original data?

Sub Sort_by_Measure()
'Sort columns left to right by Measure, then by Quarter
Application.ScreenUpdating = False
UnhideCols
Columns("E:JZ").Select
ActiveWorkbook.Worksheets("Detail").Sort.SortField s.Clear
ActiveWorkbook.Worksheets("Detail").Sort.SortField s.add
Key:=Range("E6:JZ6") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Detail").Sort.SortField s.add
Key:=Range("E5:JZ5") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Detail").Sort
.SetRange Range("E1:JZ20000")
.Header = xlNo
.MatchCase = False
.Orientation = xlLeftToRight
.SortMethod = xlPinYin
.Apply
End With
Hide_Detail_Cols
Range("A11").Select
Application.ScreenUpdating = True
End Sub