• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA to 2-step sort multiple columns

C

cvrband

New Member
Link to cross-post:
https://www.excelforum.com/excel-pr...3643-vba-to-2-step-sort-multiple-columns.html
https://www.excelforum.com/excel-pr...3643-vba-to-2-step-sort-multiple-columns.html

I have an Excel spreadsheet that I need to sort 3 columns and varying rows. What I would like is VBA code to sort all columns and rows through item 'P' by Quantity (largest to smallest). Then I need to sort all 3 columns a second time by Sort Order (smallest to largest) neglecting the items with zero quantities. The number of items will always be the same so the first sort range will always be the same.

Here is a link to the same in Google Sheets (for reference/example only): https://goo.gl/oj44Ec

I tried to record a macro for this but it only works for this specific example (Items may not always have quantities and/or the quantities will change value so the second sort range (rows) will change). Here is what the macro recorded:

Code: 
Sub Sort1()
'
' Sort1 Macro
'
'
   Range("A4:C20").Select
   ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
   ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("A5:A20") _
       , SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
   With ActiveWorkbook.Worksheets("Sheet1").Sort
       .SetRange Range("A4:C20")
       .Header = xlYes
       .MatchCase = False
       .Orientation = xlTopToBottom
       .SortMethod = xlPinYin
       .Apply
   End With
   Range("A4:C16").Select
   ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
   ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("C5:C16") _
       , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
   With ActiveWorkbook.Worksheets("Sheet1").Sort
       .SetRange Range("A4:C16")
       .Header = xlYes
       .MatchCase = False
       .Orientation = xlTopToBottom
       .SortMethod = xlPinYin
       .Apply
   End With
End Sub

Can someone provide help? Thanks!
 
Last edited:
As per forums rules :
Do not crosspost your question on multiple forums without including links here to the other threads on other forums.

A must Read …
​
 
cvrband
Without real sample file
would You change Your spreadsheet's layout?
Anyway, without sample file it would be 'nice-to-test' ... and test ... and test.
 
cvrband - this way ... and remember #2
Code: 
Sub SortSort()
    Application.ScreenUpdating = False
    s_tab = "Sheet1"
    With Sheets(s_tab)
        b_max = .Cells(.Rows.Count, 2).End(xlUp).Row
    End With
    With Sheets(s_tab).Sort
        .SortFields.Clear
        .SortFields.Add Key:=Range("A5:A" & b_max), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
        .SetRange Range("A4:C" & b_max)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
        With Sheets(s_tab)
            a_max = .Cells(.Rows.Count, 1).End(xlUp).Row
        End With
        .SortFields.Clear
        .SortFields.Add Key:=Range("B5:B" & a_max), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        .SetRange Range("A4:C" & a_max)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Application.ScreenUpdating = True
End Sub
 
Thank you vletm! That works great for the example I provided and I think it will work for my actual application which is slightly different (more rows and columns) if you could provide a bit more information ... I am a novice at VBA. Can you explain what the following statements are doing so that I understand the process?:

b_max = .Cells(.Rows.Count, 2).End(xlUp).Row

.SortFields.Add Key:=Range("A5:A" & b_max)

a_max = .Cells(.Rows.Count, 1).End(xlUp).Row

I know that you are defining "b_max" with "b_max =" but I don't understand what follows the equal sign.

Thanks!
 
cvrband
You: I have an Excel spreadsheet that I need to sort 3 columns and varying rows.
I: Without real sample file, would You change Your spreadsheet's layout?
You: here is a link to a spreadsheet similar to the one I will be using.
And now You wrote: I think it will work for my actual application which is slightly different (more rows and columns)
Is it windy or what?
Those gives the last row from needed column and use those values for range.
 
sample file <> real file. I stated 3 columns in my original post to not deviate from the simplified "sample file" that I provided. I thought that I would be able to modify the code to my specific application. I then ask for additional help to understand the code and get chastised for it. Thanks.

My actual file has columns A-F sorting A first and then F similar to the example I provided. Again, varying number of rows.
 
cvrband
Layout! Layout!
If different layout and 'I am a novice at VBA.' then
those should be same ... much easier for You.

Few hints:
Range("A4:C" >> Range("A4:F"
and
Key:=Range(" have to be correct.

No matter of number of rows, only that 'headers' has to be just same place!
 
I've adapted your code to my spreadsheet, but there is one issue that I didn't anticipate. When there is a 0 value or " " in the A column, the code uses those values in the 2nd sort with the F column (Col C in my example) rather than ignoring them like I need. If it is truly blank, the code is successful. Can you provide a solution to ignore those rows in the 2nd sort function if the cells in column A are 0 or " "? (negative numbers will not exist in this spreadsheet). Thanks
 
cvrband
One possible ...
change the beginning like below
Code: 
Sub SortSort()
    Application.ScreenUpdating = False
    s_tab = "Sheet1"
    With Sheets(s_tab)
        a_max = .Cells(.Rows.Count, 1).End(xlUp).Row
        For y = 5 To a_max
            If .Cells(y, 1) < 1 Or .Cells(y, 1) = " " Then .Cells(y, 1) = ""
        Next y
        b_max = .Cells(.Rows.Count, 2).End(xlUp).Row
    End With
    With Sheets(s_tab).Sort
 
vletm - your continued help is much appreciated.

The latest code deletes formulas in Col A that produce the 0 value or "". If the spreadsheet is updated or revised, those cells may change from 0 or "" to a positive number which can't be done if the formulas are removed.

Since the first sort populates all of the positive numbers in Col A at the top leaving the 0 and "" values below, is there a way to tell the second sort to stop the row above the first 0 or "" encountered to create the a_max value? Thanks.

(update) I can update my spreadsheet formulas to only return positive numbers or zeros in Col A, so the second sort would stop the row above the first 0 encountered to create the a_max value if that is possible and easier for coding.
 
Last edited:
cvrband
When do You start reading others Replies?
# 8 Reply
You:I have an Excel spreadsheet that I need to sort 3 columns and varying rows.
I: Without real sample file, would You change Your spreadsheet's layout?
#10 Reply
Layout! Layout!
If different layout and 'I am a novice at VBA.' then
those should be same ... much easier for You.
>>
I don't / I cannot know Your sheets layout!
That 'new part' clears those Your wanted cells as You wanted.
... You just 'forget' to tell some minor details.
>>
Have You tried to figure why Your 'the second sort would stop the row above the first 0 encountered' do not work? The first 0 could be the 1st value ... or how?
>>
There are possibilities to do many things,
but there are still some challenges to do those with eyes closed.
 
ignored not cleared... verbatim: "Can you provide a solution to ignore those rows..."

See sample spreadsheet made to give you the exact layout as you request (# of rows continue and will vary): https://app.box.com/s/s1dqybexdw46gyhqj94fy7j5mh8q4l1d

Notice: Some of the Quantity cells in column A are hand input (orange) while others have formulas (white). Some hand input cells can and will be left blank for different applications of the spreadsheet.

When final sort is completed, any Quantity that is not a value > 0, will be pushed to the bottom of the list and the sort order is insignificant. See the final sort example - Columns K - P

Purpose of VBA is to push all the items with Quantities > 0 to the top of the list and then sort those items > 0 by a pre-specified sort order value in ascending order.
 
cvrband
When do You start reading others Replies?
1) the exact layout as you request ...
that is changed, as I have written #10Reply ...
I didn't make any change of that!
because Sheet's layout seems vary every time!

2) "Can you provide a solution to ignore those rows..."
I don't need to prove ...

Code: 
Sub SortSort()
    Application.ScreenUpdating = False
    s_tab = "Sheet1"
    With Sheets(s_tab)
        a_max = .Cells(.Rows.Count, 1).End(xlUp).Row
        .Range("A4:A" & a_max).Copy
        .Range("E4").PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
        For y = 5 To a_max
            If .Cells(y, 4) <= 0 Or .Cells(y, 4) = " " Then .Cells(y, 4) = ""
        Next y
        b_max = .Cells(.Rows.Count, 2).End(xlUp).Row
    End With
    With Sheets(s_tab).Sort
        .SortFields.Clear
        .SortFields.Add Key:=Range("E5:E" & b_max), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
        .SetRange Range("A4:F" & b_max)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
        With Sheets(s_tab)
            e_max = .Cells(.Rows.Count, 5).End(xlUp).Row
        End With
        .SortFields.Clear
        .SortFields.Add Key:=Range("B5:B" & e_max), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        .SetRange Range("A4:F" & e_max)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Sheets(s_tab).Range("E4:E" & a_max).ClearContents
    Application.ScreenUpdating = True
End Sub
 
In order for me to help you help me, I need to understand what it is that you require. All of your replies were in sentence fragments and confusing, to say the least. Your keyboard has the full alphabet and punctuation for a reason. I gave you the EXACT layout in #15 and you still complain. Honestly, this concept was understandable in the very first post and the example I provided. I planned to manipulate the simplistic row and column callouts in the code for my actual needs. I do appreciate your time and effort minus the snarky and demanding remarks.
 
cvrband - okay
from #4 Reply
Without real sample file
would You change Your spreadsheet's layout?Anyway, without sample file it would be 'nice-to-test' ... and test ... and test.
You changed Your (sample) layout many times ... new samples ...
How would anyone know what is Your EXCAT layout?
#10 Reply
If different layout and 'I am a novice at VBA.' then
those should be same ... much easier for You.
If layout is different then of course my code works 'my way'.
I gave you the EXACT layout in #15
If now only difference is 'EXACT layout's header position' then
after You take few breaths, You can solve it too.
'My Code' isn't so so different than Yours #1Reply.
> You want help > You give exact information for You needs >
If someone else has to pull pull guess many details ...
It will be 'hard for You too'.
>> If You cannot give, You cannot get <<
 
You must log in or register to reply here.
Back
Top