Excel Sort A Column By Font Width ? 2522

У вашего броузера проблема в совместимости с HTML5

After sorting by number of characters in a cell, we get a variant from Thinks Too Much (b): Can you sort, factoring in the width of the letters in the font that is being used?

Table of Contents
(0:00) Welcome Back
(0:12) Question
(0:40) VBA code to measure width
(1:44) Differing fonts & sizes
(2:15) Nancy Faust

Here is my VBA solution:
Sub SortAscCustomerWidth()
FinalRow = Range(“E1048576”).End(xlUp).Row
OrigWidth = Columns(5).ColumnWidth
HelperColumn = Range(“XFD1”).End(xlToLeft).Column + 1
Cells(1, HelperColumn).Value = “Helper”
’ Loop
For Each cell In Range(“E2”).Resize(FinalRow - 1)
cell.Select
Selection.Columns.AutoFit
Cells(cell.Row, HelperColumn).Value = Columns(5).Width
Next cell
Columns(5).ColumnWidth = OrigWidth
’ Sort
Range(“A1”).Resize(FinalRow, HelperColumn).Sort Key1:=Cells(1, HelperColumn), Order1:=xlAscending, Header:=xlYes
’ Clear Helper
Cells(1, HelperColumn).Resize(FinalRow, 1).Clear
End Sub

Sub SortDescCustomerWidth()
FinalRow = Range(“E1048576”).End(xlUp).Row
OrigWidth = Columns(5).ColumnWidth
HelperColumn = Range(“XFD1”).End(xlToLeft).Column + 1
Cells(1, HelperColumn).Value = “Helper”
’ Loop
For Each cell In Range(“E2”).Resize(FinalRow - 1)
cell.Select
Selection.Columns.AutoFit
Cells(cell.Row, HelperColumn).Value = Columns(5).Width
Next cell
Columns(5).ColumnWidth = OrigWidth
’ Sort
Range(“A1”).Resize(FinalRow, HelperColumn).Sort Key1:=Cells(1, HelperColumn), Order1:=xlDescending, Header:=xlYes
’ Clear Helper
Cells(1, HelperColumn).Resize(FinalRow, 1).Clear
End Sub