Modern Magic - How Do They Do It?

To many, this would be a trivial case (this is 100% expected), and, for those who know the nitty gritty of how performance improved going from 10 billion to 175 billion parameters, this is REALLY trivial, but, it still blows my mind that it caught THIS 😊

Q: I am using the below VBA macro to sort a column in descending order, but it has the side effect of doing the equivalent of CTRL+HOME, so I have to manually pan (scroll horizontally) to get back to the column of interest. What can I do?

🔹 Original VBA Macro:

Sub sort_col_desc()
    Dim TableName As String
    Dim TableHeader As String
    
    TableName = ActiveCell.ListObject.Name
    TableHeader = ActiveCell.ListObject.HeaderRowRange.Cells(1, ActiveCell.Column - ActiveCell.ListObject.Range.Column + 1).Value
    
    With ActiveCell.ListObject.Sort
        .SortFields.Clear
        .SortFields.Add2 Key:=Range(TableName & "[[#All],['" & TableHeader & "]]"), SortOn:=xlSortOnValues, _
            Order:=xlDescending, DataOption:=xlSortNormal
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .Apply
    End With
End Sub

A: The side effect you're observing — where Excel jumps to cell A1 (like pressing Ctrl+Home) after the sort — is a known quirk when using ListObject.Sort. To keep your view anchored where you started, you can capture the active cell’s location before the sort, and then scroll back to it afterward.

You can do this using the Application.Goto method. Here’s how to modify your macro so that it restores the view after sorting:

✅ Improved VBA Macro (Preserves Scroll Position):

Sub sort_col_desc()
    Dim TableName As String
    Dim TableHeader As String
    Dim TargetCell As Range
    Dim scrollRow As Long, scrollCol As Long

    ' Save current scroll position
    With ActiveWindow
        scrollRow = .ScrollRow
        scrollCol = .ScrollColumn
    End With

    ' Remember the active cell to avoid selection jumps
    Set TargetCell = ActiveCell

    ' Get table and column info
    TableName = TargetCell.ListObject.Name
    TableHeader = TargetCell.ListObject.HeaderRowRange.Cells(1, _
        TargetCell.Column - TargetCell.ListObject.Range.Column + 1).Value

    ' Sort the table
    With TargetCell.ListObject.Sort
        .SortFields.Clear
        .SortFields.Add2 Key:=Range(TableName & "[[#All],[" & TableHeader & "]]"), _
            SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .Apply
    End With

    ' Restore scroll position
    With ActiveWindow
        .ScrollRow = scrollRow
        .ScrollColumn = scrollCol
    End With

    ' Restore selection (optional)
    TargetCell.Select
End Sub

Highlights:

• Fixed the stray single quote in the sort range: [" & TableHeader & "]
• TargetCell stores your current selection so we can return to it
• Application.Goto with Scroll:=True scrolls back without disrupting selection behavior