Link to home
Create AccountLog in
Avatar of RWayneH
RWayneHFlag for United States of America

asked on

Decoding a line of VBA DataSort

WS.Range("B3:C" & MaxRow).Sort Key1:=WS.Range("C3"), order1:=xlAscending, MatchCase:=False, Header:=xlYes

Does WS need to have a value?
I am trying to sort a dataset by column F, that starts at row 2 with headers and I wanted to use this for a dynamic range.

Trying to sort Largest to Smallest

This is what the recorder recorded.
       ActiveWorkbook.Worksheets("CountSummary").Sort.SortFields.Clear
        ActiveWorkbook.Worksheets("CountSummary").Sort.SortFields.Add Key:=Range( _
            "F2:F89"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
            xlSortNormal
        With ActiveWorkbook.Worksheets("CountSummary").Sort
            .SetRange Range("A1:Y89")
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With

Open in new window


Ln 6 will not work, in a dynamic dataset.  Any ideas?
Avatar of Shums Faruk
Shums Faruk
Flag of India image

Hi Rwayneh,

Please post your full code, its difficult to understand with single line, anyway you can try below code:
Sub SortData()
Dim Ws As Worksheet
Dim LR As Long
Application.ScreenUpdating = True
Set Ws = ActiveSheet
LR = Ws.Range("B" & Rows.Count).End(xlUp).Row

Ws.Sort.SortFields.Clear
    Ws.Sort.SortFields.Add Key:=Range("F2:F" & LR), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With Ws.Sort
        .SetRange Range("A2:F" & LR)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
Application.ScreenUpdating = True
End Sub

Open in new window

Avatar of RWayneH

ASKER

Looks like this is very close to working.
Dim Ws As Worksheet
Dim LR As Long
'Application.ScreenUpdating = True
Set Ws = ActiveSheet
LR = Ws.Range("B" & Rows.Count).End(xlUp).Row

Ws.Range("A1:P" & LR).Sort Key1:=Ws.Range("A1"), order1:=xlAscending, MatchCase:=False, Header:=xlYes

Open in new window


but it did not sort by Col F,  looks like col A??
Because you are specifying Key1:=Ws.Range("A1")
Try below:
Sub SortDataOnMultipleColumns()
Dim Ws As Worksheet
Dim LR As Long
Application.ScreenUpdating = False
Set Ws = Worksheets("CountSummary")
LR = Ws.Range("A" & Rows.Count).End(xlUp).Row
Ws.Sort.SortFields.Clear
    Ws.Sort.SortFields.Add Key:=Range("F1:F" & LR), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With Ws.Sort
        .SetRange Range("A1:P" & LR)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
Application.ScreenUpdating = True
End Sub

Open in new window

Avatar of RWayneH

ASKER

Works... any idea how to reduce it down to the one line version?
My friend, you seems like to play with fire. LOL
Not sure but try below:
Ws.Columns("A:P").Sort key1:=Range("F:F"), order1:=xlAscending, Header:=xlYes

Open in new window

Avatar of RWayneH

ASKER

I am the FireStarter with the Eternal overflow cup.  :-)   Your code works.  I was just trying to understand how that one line of code worked.
Avatar of RWayneH

ASKER

One line of code worked..  Final product looked like this:

Dim Ws As Worksheet
Dim LR As Long
Application.ScreenUpdating = False
Set Ws = Worksheets("CountSummary")
LR = Ws.Range("A" & Rows.Count).End(xlUp).Row
Ws.Sort.SortFields.Clear
Ws.Columns("A:P").Sort key1:=Range("F:F"), order1:=xlDescending, Header:=xlYes

Open in new window


I was unsure about the key1 and the order1  ???  but it did not seem to matter, in this case.
ASKER CERTIFIED SOLUTION
Avatar of Shums Faruk
Shums Faruk
Flag of India image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of RWayneH

ASKER

EXCELent!!  Thanks for the help.
You're Welcome RWayneH! Glad I was able to help :)