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.

 

[REUBEN]

Yes, Peter.

It is the same file. Are you able to see IF formulas in the blank rows? So although there is text in them (with the IF formulas), it doesnt show up obviously to the user, unless they click on those cells.

This is only my guess, but the reason to bring this up is that perhaps your new code is looking at rows that do not have any text/contents or in other words are actually blank?

 

[Peter_SSs]

Are you able to see IF formulas in the blank rows?

Yes, I do see the formulas in the first 2 and last 2 columns, but those formulas are all returning null strings "".

Did you try the modified line of code?

 

[Peter_SSs]

Also, just to double-check. Did you actually copy/paste the v3 code from post #28 or manually modify from the earlier codes?
I want to be sure that you have these changed values from the earlier codes.

.Sort Key1:=.Columns(2), Order1:=xlAscending, Key2:=.Columns(3), Order2:=xlAscending, _
          Key3:=.Columns(4), Order3:=xlAscending, Header:=xlYes, Orientation:=xlTopToBottom

 

[REUBEN]

Also, just to double-check. Did you actually copy/paste the v3 code from post #28 or manually modify from the earlier codes?
I want to be sure that you have these changed values from the earlier codes.

.Sort Key1:=.Columns(2), Order1:=xlAscending, Key2:=.Columns(3), Order2:=xlAscending, _
          Key3:=.Columns(4), Order3:=xlAscending, Header:=xlYes, Orientation:=xlTopToBottom

I copied the new code of V3. Strange that it works on your system but messes up on mine.

Okay just so I understand I am not doing anything wrong. Here's how I am accessing this code. I've first of all added this code in tho the workbook and not on the sheet where the entries are made. Then, on the sheet I inserted an button like object and assigned the macro to it. Then went ahead and created three new blank rows under my last row. And finally ran the code by clicking the button.

All data is then sorted out but the empty rows go right to the top of the table, followed by my last row of text and then the rest of the sorted data.

What am i doing wrong?

 

[Peter_SSs]

I'm not sure why that happens but will try to resolve if possible.

The first thing is that you still haven't answered

Did you try the modified line of code?

That is, the modified line from post #30 .

If you have done that and it still fails could you provide a link to the current version of the test file that has
- the modified v3 code in it
- the 2 or 3 blank rows already created at the end of the table

I would like to be able to click the button on the sheet myself to see if I can resolve what is happening.

 

[REUBEN]

I'm not sure why that happens but will try to resolve if possible.

The first thing is that you still haven't answered That is, the modified line from post #30 .

If you have done that and it still fails could you provide a link to the current version of the test file that has
- the modified v3 code in it
- the 2 or 3 blank rows already created at the end of the table

Sorry I was not clear in my reply. But I meant that I copied the whole code you provided for the Version 3 and replaced the old one. Then re-assigned the button (on top center of the sheet1) to run the macro.

Here's a link to the test sheet I am using - https://drive.google.com/file/d/0B8NyjX9raccOZzRMekxCNVZlWVE/view?usp=sharing

Thanks again for looking in to this.

 

[Peter_SSs]

1. It looks like you never did try the change I suggested in post #30 , but I don't think it would have helped.

2. I opened your file, clicked the button and the sort happened perfectly. Sort of and sort of

3. I don't know if there is a reason you have the code in the ThisWorkbook module. I would have it in a standard module, but again, I don't think that is the problem.

So, I still don't know why it works for me and not for you. never-the-less let's give it one more try with this in a standard module & linked to your button.

Sub Sort_Table_v4()
  Dim lo As ListObject
  Dim lr As Long
  
  Application.ScreenUpdating = False
  Set lo = ActiveSheet.ListObjects("Table1")
  lo.ListColumns.Add Position:=2
  With lo.Range
    lr = .Find(What:="*", LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, SearchFormat:=False).Row - .Row
    .Cells(2, 2).FormulaR1C1 = "=IF(ROWS(R3C:RC)<" & lr & ",[@[" & .Cells(1, 1).Value & "]],""zzz""&TEXT(ROW(),""0000""))"
    .Columns(2).Value = .Columns(2).Value
  End With
  With lo.Sort.SortFields
    .Clear
    .Add Key:=Range("Table1[Column1]"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    .Add Key:=Range("Table1[Service]"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    .Add Key:=Range("Table1[Tasks]"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
  End With
  With lo.Sort
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
  End With
  lo.ListColumns(2).Delete
  Columns("F:H").HorizontalAlignment = xlCenter
  Application.ScreenUpdating = True
End Sub

 

[REUBEN]

Regarding point 1 where you're right I did not use the edit you provided in the post 30#. I'm sorry I seem to have overlooked that.

I'm not sure I follow what you mean by

[COLOR=#333333]2. I opened your file, clicked the button and the sort happened perfectly. [B][U]Sort of [/U][/B][/COLOR][B][U]:smile:[COLOR=#333333] and sort of [/COLOR]:([/U][/B]

Regarding why I inserted the code in the workbook is perhaps I misunderstood your instructions in post #11 , where you said "OK, then try (on a copy of your workbook):".

All the above aside, I have now inserted the code you provided in to a module and removed the earlier code from the workbook, reconnected the macro button, and something different happens this time. The data is sorted but once again the empty rows jump right up to the top, followed by the text row of mine and for some weird reason, the first of Steve Jobs' entry remains right on the top, above the empty rows. Here's a screenshot of what I see - https://drive.google.com/file/d/0B8NyjX9raccOODZRTkdfdkRqVGc/view?usp=sharing

And here's the updated file - https://drive.google.com/file/d/0B8NyjX9raccOUmwtd25vWkcwV0U/view?usp=sharing

I've tried this a few times now, but I still get the same error. I too cannot understand why this may be happening, but as you can see in the screenshot I've shared, the code really doesnt seem to work right on my end. Could there be anything different from the point of view that my sheet is being worked on with Microsoft Excel's Swedish/Danish settings?

Thanks.

 

[Peter_SSs]

Could there be anything different from the point of view that my sheet is being worked on with Microsoft Excel's Swedish/Danish settings?

I suppose that could be a possibility, but I am noy familiar with working with different language versions.

One more suggestion. On the latest test file (v4), put this macro (it is basically the first part of the previous macro) in a standard module, place your cursor anywhere in this code and press F5 to run it.

Sub Test()
  Dim lo As ListObject
  Dim lr As Long
  

  Set lo = ActiveSheet.ListObjects("Table1")
  lo.ListColumns.Add Position:=2
  With lo.Range
    lr = .Find(What:="*", LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, SearchFormat:=False).Row - .Row
    .Cells(2, 2).FormulaR1C1 = "=IF(ROWS(R3C:RC)<" & lr & ",[@[" & .Cells(1, 1).Value & "]],""zzz""&TEXT(ROW(),""0000""))"
    .Columns(2).Value = .Columns(2).Value
  End With
End Sub

Now look at your sheet. The table should have an extra column, 2nd from left. That column should contain exactly what is in the first column down as far as worksheet row 10 (the row above your 'text' row).
From then on, the cells should contain
zzz0011 (in your 'text' row)
zzz0012
zzz0013
zzz0014

Do you have that?

The idea I was using was to use this column as the primary sort column which should keep those zzz rows at the bottom.


Ah, just a thought on the language version. What is the heading in the new column? For me it is 'Column1' and I have used that in a subsequent line in the v4 macro

.Add Key:=Range("Table1[Column1]"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

If yours is something other than Column1, you would need to change the code to match. What about the 'Service' and 'Tasks' columns? Are those the actual names in the sheet for you or do you have a Swedish equivalent?

 

[REUBEN]

Okay, just to clarify the language thing - my system's default language is English. However, as the location of the server that I am logging in to to work on this sheet is Sweden. And that adds some finer setting in to the microsoft applications. for example, the decimal point separator is a comma "," instead of a dot "." and so on.

So I am using exactly the same workbook as I have shared with you and the contents are identical.

Now after trying the above code, the table has an extra column, 2nd from left. However, that column contains only the first row's data, i.e. the Steve Jobs' entry in the first row and not exactly what is in the first column . The just a bunch of blank fields down till the last row (including the row above my 'text' row). But not the text you describe below:

zzz0011 (in your 'text' row)
zzz0012
zzz0013
zzz0014​

So I do not have the above.

As regards the heading in the new column. I too have 'Column1'.

Guess it is easier to use a code to lock all other cells below the table and restrict the user to follow my 'text' row instructions and insert new row or insert a new row by hitting tab at teh end of a row. That means I go back to the first version of your code. Which works perfectly of course!