Excel Sort A Column By Font Width ? 2522

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

Transcript of the video:

Well now we're on the edge of just ludicrousness. Episode 2522, Sort a Column by Width of Characters.
Including the font face and font size.
Just this morning, three hours ago, episode 2521, they wanted to sort a column by the number of characters and ThinkTooMuchB says, Well wait, what if it's different fonts, or a W is wider than an I?
Okay, sure. Let's do it.
Alt F11. I have two bits of code here.
I'll put these down in the YouTube description.
We're targeting column E and we're going to build a helper column in column J.
What we're going to do, I'll just press F8 here, is we measure the length of column E before we start.
And then we're going to loop through each cell in column E, select the cell, do an AutoFit, see what the width is, and record it over in column J and then go again.
Bam, bam, bam, bam, bam. All right.
This is boring. Actually we'll run to this point.
So debug, Run to Cursor. There we go.
So now we've applied all of those helper cells. Make column E back to the original column.
Do a sort. Look at that.
It's beautiful.
So this has six I's, this has six O's, six N's, six M's, and six W's, and it goes in sequence like that.
How cool is that? Then we clear the helper cell.
So I have two different versions here.
One to sort descending and then one to sort ascending.
Now you wouldn't want to do this with a hundred thousand rows, but just for fun here on a Saturday is pretty cool.
This one we're actually changing, so these all have six A's, Arial Narrow, Arial, and Rockwell Extra Bold. These all have six B's.
This at 11 point, this at 14 point, this at 18 point.
We'll try and run ascending.
Looks great. Try and run descending.
Looks great.
It sounded like a ridiculous question and then even more ridiculous to look at the font width, but there's a way to do it.
Thanks to ThinkTooMuchB for that awesome question, and thanks to you for stopping by.
We'll see you next time for another netcast from MrExcel.