Sorting key

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
1,826
When you are sorting using range.sort method, one of the parameters is Key. If I want to sort by column A, with the header being in row 3 and the data to sort starting in row 4. What would the key be, is it A3?
 

Some videos you may like

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

smozgur

BatCoder
Joined
Feb 28, 2002
Messages
1,209
Yes, but the range to be sorted should not include the header row. Like the following:

VBA Code:
Sub sortRangeA4C6()
    Range("A4:C6").Sort Key1:=Range("A3")
End Sub
 

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
1,826
This is the code I have

VBA Code:
                wsDst.Sort.SortFields.Clear
                wsDst.Sort.SortFields.Add Key:=Range("A3"), _
                    SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
                        With Workbooks(DocYearName).Worksheets(Combo).Sort
                            'set range to sort of A3 to AO
                            .SetRange Range("A4:AO" & lr)
                            .header = xlYes
                            .MatchCase = False
                            .Orientation = xlTopToBottom
                            .SortMethod = xlPinYin
                            .Apply
                        End With


wsDst is defined as the correct sheet
VBA Code:
Set wsDst = Workbooks(DocYearName).Worksheets(Combo)

lr = wsDst.Cells.Find("*", , xlValues, , xlRows, xlPrevious).Row
 

smozgur

BatCoder
Joined
Feb 28, 2002
Messages
1,209

ADVERTISEMENT

If you use Range() without parent object, then it refers to the active sheet's range.

So, change the code to add wsDst parent to the Range() properties in your code. Two places I can see.
Also, you are setting .Header = xlYes, so the sort range should start from A3.

VBA Code:
Key:=wsDst.Range("A3")

.SetRange wsDst.Range("A3:AO" & lr)

And, you are using multiple references for the worksheet, not necessary, and actually more complicated. It might cause mistakes.
I don't know the entire code but normalized version of the snipped you sent could be something like this:

VBA Code:
With wsdst.Sort
    With .SortFields
        .Clear
        .Add Key:=wsdst.Range("A3"), _
            SortOn:=xlSortOnValues, _
            Order:=xlAscending, _
            DataOption:=xlSortNormal
    End With
    'set range to sort of A3 to AO
    .SetRange wsdst.Range("A3:AO" & lr)
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With
 

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
1,826
Thanks for the reply smozgur. It didn't work so I thought I should probably show you all the code for the procedure. I don't think the sort code will work as there may be several wsDst files that the rows are copied to and wsDst could be changed on each iteration of the loop.


VBA Code:
Sub cmdCopy()
    Dim wsDst As Worksheet, wsHours As Worksheet, wsTrack As Worksheet, worker As String, wsSrc As Worksheet, tblrow As ListRow
    Dim Combo As String, sht As Worksheet, tbl As ListObject
    Dim lastrow As Long, DocYearName As String, Site As String, lr As Long, HoursRow As Long, lrTrack As Long
    Dim RowColor As Long, w As Window, r As Long, HoursRegister As String, ReportTracking As String
        Application.ScreenUpdating = False
       
    'assign values to variables
    Set tbl = ThisWorkbook.Worksheets("Costing_tool").ListObjects("tblCosting")
    Set sht = ThisWorkbook.Worksheets("Costing_tool")
    Site = ThisWorkbook.Worksheets("Start_here").Range("H9").Value
    'Check if each row has a date, service and requesting organisation
    For Each tblrow In tbl.ListRows
        If tblrow.Range.Cells(1, 1).Value = "" Or tblrow.Range.Cells(1, 5).Value = "" Or tblrow.Range.Cells(1, 6).Value = "" Then
            MsgBox "The Date, Service or Requesting Organisation has not been entered for every record in the table"
            Exit Sub
        End If
    Next tblrow
    For Each tblrow In tbl.ListRows
        'Define combo as the month to be recorded in
        Combo = tblrow.Range.Cells(1, 26).Value
        'If column 8 for the row is blank...
        If Not tblrow.Range(1, 8).Value = "" Then
            'worker variable is defined as the value in column 8 of the row
            worker = tblrow.Range.Cells(1, 8).Value
        Else
            'otherwise, "not allocated" is assigned to the worker variable.
            'this is used in the hours register to identify which sheet to place the hours in
            worker = "Not allocated"
        End If
        'defines HoursRegister as the hours register filename that is stored in column 38 for the row
        HoursRegister = tblrow.Range.Cells(1, 38)
        'defines ReportTracking as the report tracking filename that is stored in column 39 for the row
        ReportTracking = tblrow.Range.Cells(1, 39)
            Select Case Site
                Case "W"
                    Select Case tblrow.Range.Cells(1, 6).Value
                        Case "AW", "AWAG", "AA", "ASC", "Y"
                            DocYearName = tblrow.Range.Cells(1, 37).Value
                        Case Else
                            DocYearName = tblrow.Range.Cells(1, 36).Value
                    End Select
                Case "R"
                    Select Case tblrow.Range.Cells(1, 6).Value
                        Case "AW", "AWAG", "AA", "ASC", "Y"
                            DocYearName = tblrow.Range.Cells(1, 42).Value
                        Case Else
                            DocYearName = tblrow.Range.Cells(1, 36).Value
                    End Select
            End Select
        If Not isFileOpen(DocYearName & ".xlsm") Then Workbooks.Open ThisWorkbook.Path & "\" & "Work Allocation Sheets" & "\" & Site & "\" & DocYearName & ".xlsm"
        'If Not isFileOpen(HoursRegister & ".xlsm") Then Workbooks.Open ThisWorkbook.Path & "\" & "Hours Register" & "\" & Site & "\" & HoursRegister & ".xlsm"
        If Not isFileOpen(ReportTracking & ".xlsm") Then Workbooks.Open ThisWorkbook.Path & "\" & "Report Tracking" & "\" & Site & "\" & ReportTracking & ".xlsm"
        'Set wsHours = Workbooks(HoursRegister).Worksheets(worker)
        Set wsDst = Workbooks(DocYearName).Worksheets(Combo)
        Set wsTrack = Workbooks(ReportTracking).Worksheets(Combo)
        lr = wsDst.Cells.Find("*", , xlValues, , xlRows, xlPrevious).Row
        lrTrack = wsTrack.Cells.Find("*", , xlValues, , xlRows, xlPrevious).Row
        'With wsHours
              'this copies the date column in the tblCosting
            'HoursRow = .Range("A" & Rows.Count).End(xlUp).Row
            'tblrow.Range(, 1).Copy
            'this pastes it into column A of hours register file
            '.Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteFormulasAndNumberFormats
              'this copies the YP name column in the tblCosting
            'tblrow.Range(, 4).Copy
            'this pastes it into column B of hours register file
            '.Range("B" & HoursRow).Offset(1).PasteSpecial xlPasteFormulasAndNumberFormats
               'this copies the YP name column in the tblCosting
            'tblrow.Range(, 3).Copy
            'this pastes it into column A of hours register file
            '.Range("C" & HoursRow).Offset(1).PasteSpecial xlPasteFormulasAndNumberFormats
               'this copies the hours column in the tblCosting
            'tblrow.Range(, 9).Copy
            'this pastes it into column A of hours register file
            '.Range("D" & HoursRow).Offset(1).PasteSpecial xlPasteFormulasAndNumberFormats
        'End With
        With wsTrack
            'this copies the date column in the tblCosting
            tblrow.Range(, 1).Copy
            'this pastes it into column A of report tracking file
            .Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
              'this copies the YP name column in the tblCosting
            tblrow.Range(, 4).Copy
            'this pastes it into column B of report tracking file
            .Range("A" & Rows.Count).End(xlUp).Offset(, 1).PasteSpecial xlPasteValues
               'this copies the YP name column in the tblCosting
            tblrow.Range(, 5).Copy
            'this pastes it into column A of report tracking file
            .Range("A" & Rows.Count).End(xlUp).Offset(, 2).PasteSpecial xlPasteValues
        End With
        With wsDst
                'This sets column width of request number column so it can be read and is not xxxxx
                .Columns("C:C").ColumnWidth = 8
                'This copies the first 7 columns, i.e. A:G, of the current row of the table to column A in the destination sheet.
                tblrow.Range.Resize(, 7).Copy
                'This pastes in the figures in the first 7 columns starting in column A
                .Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
                'This copies the first 7 columns, i.e. A:G, of the current row of the table to column A in the destination sheet.
                tblrow.Range(, 10).Copy
                'This pastes in the figures in the first 7 columns starting in column A
                .Range("H" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
               
                'Overwrites the numbers pasted to column I with a formula
                .Range("I" & Rows.Count).End(xlUp).Offset(1).Formula = "=IF(RC[-4]=""Activities"",0,RC[-1]*0.1)"
                'Overwrites the numbers pasted to column L with a formula
                .Range("J" & Rows.Count).End(xlUp).Offset(1).Formula = "=RC[-1]+RC[-2]"
                'Adds currency formatting to total ex gst column
                .Columns(8).NumberFormat = "$#,##0.00"
                'Adds Australian date format to date column
                '.Range("A:A").NumberFormat = "dd/mm/yyyy"
        End With
    Next tblrow
   
            With wsDst.Sort
                With .SortFields
                    .Clear
                    .Add Key:=wsDst.Range("A3"), _
                        SortOn:=xlSortOnValues, _
                        Order:=xlAscending, _
                        DataOption:=xlSortNormal
                End With
                'set range to sort of A3 to AO
                .SetRange wsDst.Range("A3:AO" & lr)
                .header = xlYes
                .MatchCase = False
                .Orientation = xlTopToBottom
                .SortMethod = xlPinYin
                .Apply
            End With
               
            With wsTrack.Sort
                With .SortFields
                    .Clear
                    .Add Key:=wsTrack.Range("A1"), _
                        SortOn:=xlSortOnValues, _
                        Order:=xlAscending, _
                        DataOption:=xlSortNormal
                End With
                'set range to sort of A1 to I
                .SetRange wsTrack.Range("A1:I" & lrTrack)
                .header = xlYes
                .MatchCase = False
                .Orientation = xlTopToBottom
                .SortMethod = xlPinYin
                .Apply
            End With
   
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
End Sub
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
19,456
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

So, as mentioned in the other post....does lr return the correct amount of lines in this line of code
VBA Code:
.SetRange wsDst.Range("A3:AO" & lr)
 

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
1,826
lr returns 6 and I looked in one of the worksheets defined by wsDst and it only had 4 lines.
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
19,456
Office Version
  1. 2013
Platform
  1. Windows
So you need to refresh you lr

VBA Code:
lr = wsDst.Cells.Find("*", , xlValues, , xlRows, xlPrevious).Row
            With wsDst.Sort
                With .SortFields
                    .Clear
                    .Add Key:=wsDst.Range("A3"), _
                        SortOn:=xlSortOnValues, _
                        Order:=xlAscending, _
                        DataOption:=xlSortNormal
                End With
                'set range to sort of A3 to AO
                .SetRange wsDst.Range("A3:AO" & lr)
                .header = xlYes
                .MatchCase = False
                .Orientation = xlTopToBottom
                .SortMethod = xlPinYin
                .Apply
            End With
 

Watch MrExcel Video

Forum statistics

Threads
1,118,126
Messages
5,570,332
Members
412,319
Latest member
akshat1231
Top