J
jday
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.SortFields.Clear
ActiveWorkbook.Worksheets("Detail").Sort.SortFields.add
Key:=Range("E6:JZ6") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Detail").Sort.SortFields.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
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.SortFields.Clear
ActiveWorkbook.Worksheets("Detail").Sort.SortFields.add
Key:=Range("E6:JZ6") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Detail").Sort.SortFields.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