Custom Sorting via VBA

[REUBEN]

Hi All,

I need to sort three columns on a condition that the First column 'B', is sorted in alphabetical ascending order, then column 'C' is sorted based on the texts is contains and finally column 'D' is sorted with the texts it contains.

Background on the sheet (only FYI), column 'B' is the name of customers, column 'C' is the service we provided them and column 'D' is the specific task we did for them within the respective service. And the options in column 'B' and 'D' are provided via a drop down menu. So the text phrases/options remains the same.

I am unable to script anything in VBA and I understand the above would need to be coded in to VBA.

Thank you for helping me out.

 

[Peter_SSs]

Are you referring to whether the data in the following columns must be shuffled as well? Then Yes, they too must be shuffled accodingly.

OK, then try (on a copy of your workbook):

Sub mySort()
  Range("B1", Cells.SpecialCells(xlLastCell).Offset(-1)).Sort Key1:=Range("B2"), Order1:=xlAscending, Key2:=Range("C2"), _
    Order2:=xlAscending, Key3:=Range("D2"), Order3:=xlAscending, Header:=xlYes, Orientation:=xlTopToBottom
End Sub

 

[REUBEN]

Yes, but it depends on the answers to my previous questions.

Edit: OK, we posted at about the same time.
Still, can you confirm that the data beyond column D gets rearranged, along with the B:D data?

Yes, it shuffles around but remains intact.

 

[REUBEN]

Thanks Peter.

Unfortunately, the code throws up an Runtime Error 1004. Saying the "Sort method of range class failed".

And that could be as the table's header row is on row 2 and the actual data from row 3. I also tried adding the code to the workbook and in a module, but neither of that seems to work.

 

[REUBEN]

You need to answer this question :

To set the sort range, what column can be used to find the last row?

Hi,

We could for instance use column B to sort the data, that is also like a primary key column I am using.

 

[Peter_SSs]

And that could be as the table's header row is on row 2 and the actual data from row 3.

Is this a formal Excel table? Insert -> Table -> Create table
Or just your own columns of data with headings in row 2?

What happens if you change the "B1" in my code to "B2"?

 

[REUBEN]

Is this a formal Excel table? Insert -> Table -> Create table
Or just your own columns of data with headings in row 2?

It is a formal Excel Table. Has defined headers, and a custom colour pattern.

Edit: Sorry, about changing "B1" to "B2", it still gives out an error. And now I have tried copying the code under the workbook, copy of the workbook, within the sheet itself and in a module even. but it gives me either the above error or same error '1004' with error message "Application-defined or object-defined error".

 

[Peter_SSs]

It is a formal Excel Table. Has defined headers, and a custom colour pattern.

And the text that has to be excluded from the sort - is that the last row of the table?

 

[REUBEN]

And the text that has to be excluded from the sort - is that the last row of the table?

Yes, it is the last row of that table with Text. Some times I have seen that some users do not hit the "Tab" key to jump to the next row/ to create a new row. and simply click on the next row and start inserting their data. This ofcourse does not get included within the formal table. so I need to do a little editing of the data everytime. So, I just add in some blank rows of the formal table. so even if the user clicked on the next row then that is still part of the formal table, and not outside of it.

 

[FormR]

it is the last row of that table with Text.

Hi, just a thought, if you are not already using one, can you not add a "Total Row" to your table and put your text there. The total row will not be affected by a sort.

 

[Peter_SSs]

I'm not a real expert on Tables (ListObjects) but I don't think you can exclude a row from the sort. Happy to be shown to be wrong though.

My work-around is to remove the last row of the table that has any data (your text row), sort the table and then reinsert the text.
Perhaps somebody else will have a more direct method.

Sub Sort_Table()
  Dim lr As Long
  Dim a As Variant
  
  Application.ScreenUpdating = False
  With ActiveSheet.ListObjects("Table1").Range
    lr = .Find(What:="*", LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, SearchFormat:=False).Row - .Row + 1
    a = .Rows(lr).Value
    .Rows(lr).ClearContents
    .Sort Key1:=.Columns(1), Order1:=xlAscending, Key2:=.Columns(2), Order2:=xlAscending, _
          Key3:=.Columns(3), Order3:=xlAscending, Header:=xlYes, Orientation:=xlTopToBottom
    .Rows(lr).Value = a
  End With
  Application.ScreenUpdating = True
End Sub

If your table name is not "Table1" then adjust that in the code.
If you only have one table on the sheet then an alternative would be to change that line to

With ActiveSheet.ListObjects(1).Range