Sorting 4 Keys of Excel file

Question

There is a requirement of 4 key fields to be sorted in Excel file (2013). Given below is the code. But not getting the desired results. Need your help to resolve the issue.

Sub ToSortFile()
    Dim lastRow, LastCol As Long

    lastRow = Cells(Rows.Count, 2).End(xlUp).Row
    LastCol = Cells(2, Columns.Count).End(xlToLeft).Column
    Set source_data = Range(Cells(2, 2), Cells(lastRow, LastCol))
    Set Ws = Sheets("Details")
    With Ws.Sort
        .SortFields.Clear
        .SortFields.Add Key:=Ws.Range("B3"), Order:=xlAscending
        .SortFields.Add Key:=Ws.Range("C3"), Order:=xlAscending
        .SortFields.Add Key:=Ws.Range("K3"), Order:=xlAscending
        .SortFields.Add Key:=Ws.Range("M3"), Order:=xlAscending
        .SetRange Ws.Range("B3:U" & lastRow)
        .Header = xlYes
        .Apply
    End With
End Sub

Answer 1

Sort a Range

• Using Option Explicit will force you to declare all variables (Dim ws As Worksheet, Dim source_data As Range).

---

• Declaring variables in one line is not recommended but if you can't help it you need to do it properly:

  Dim LastRow As Long, LastCol As Long, i As Long, Num As Double
  

  because in Dim lastRow, LastCol As Long, LastRow is declared As Variant.

---

• When you write Set ws = Sheets("Details"), Sheets is not qualified i.e. it refers to the active workbook (ActiveWorkbook), the workbook you're looking at which may be the correct workbook or it may not.

• If the worksheet you're working with is in the workbook containing the code, then you want to qualify it by using ThisWorkbook:

  Dim ws As Worksheet: Set ws = ThisWorkbook.Worksheets("Details")
  

• If it is not, then you want to qualify each worksheet in the following way:

  Dim wb As Workbook: Set wb = Workbooks("Test.xlsx")
  

  or if the workbook is closed, to open it ...

  Dim wb As Workbook: Set wb = Workbooks.Open("C:\Test\Test.xlsx")
  

  ... and continue with...

  Dim ws As Worksheet: Set ws = wb.Worksheets("Details")  
  

• Note that there are cases when you have to use ActiveWorkbook.

---

• When you write lastRow = Cells(Rows.Count, 2).End(xlUp).Row, Cells and Rows are not qualified i.e. they refer to the active sheet (ActiveSheet) which may be worksheet Details or it may not.

• To qualify it you can use the following:

  Dim ws As Worksheet: Set ws = ThisWorkbook.Worksheets("Details")
  Dim LastRow As Long: LastRow = ws.Cells(ws.Rows.Count, 2).End(xlUp).Row      
  

---

• A rule of thumb is to be explicit as often as possible (reasonable) i.e. don't let Excel decide for you.
• You won't use .Header = xlGuess but you will use xlYes when the range includes the headers or xlNo when it doesn't. In your particular case, you were referencing only the data (Ws.Range("B3:U" & lastRow)) i.e. it didn't include the headers so you should have used xlNo.
• Related to the Keys argument, although it is enough to specify any cell within the column to be sorted, I would recommend specifying the whole column i.e. instead of B2 use B2:BLastRow, 2 being the header row.

---

• Note that the sorting is performed in the order from the last added sort field to the first i.e. in your case the order is M, K, C, and B i.e. it still could be wrong for you.

Option Explicit

Sub ToSortFile()

    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    
    Dim ws As Worksheet: Set ws = wb.Worksheets("Details")
    
    Dim LastRow As Long: LastRow = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row
    Dim LastCol As Long
    LastCol = ws.Cells(2, ws.Columns.Count).End(xlToLeft).Column
    
    Dim srg As Range: Set srg = ws.Range("B2", ws.Cells(LastRow, LastCol))
    
    With ws.Sort
        .SortFields.Clear
        .SortFields.Add Key:=srg.Columns(1), Order:=xlAscending
        .SortFields.Add Key:=srg.Columns(2), Order:=xlAscending
        .SortFields.Add Key:=srg.Columns(10), Order:=xlAscending
        .SortFields.Add Key:=srg.Columns(12), Order:=xlAscending
        .SetRange srg
        .Header = xlYes
        .Apply
    End With

End Sub