Code for DataSorting a Table

MeisterConrad

New Member
Joined
Jan 17, 2017
Messages
11
The last time I posted code, someone told me I was posting it incorrectly. I hope I have posted it correctly this time. If I'm still doing it wrong, please forgive me, and tell me how to do it right.

I have the following code for sorting table data, but something's wrong with the way I'm referencing the sort keys.
As there are several tables on the sheet, the first bit identifies the table to be sorted.
The third bit is the code for the data-sort operation.
The middle chunk is where I'm guessing my problem is. I think my syntax in setting rng1 and rng2 is faulty. Can anybody set me straight?
And also, if there is a simpler/more efficient/elegant way of doing this, I'm all for suggestions.
Thanx!

VBA Code:
Dim LedgerNumber As String
Dim X As Integer
If InStr(ActiveCell.Text, "Table") > 0 Then
LedgerNumber = Mid(ActiveCell, 6, 2)
X = CInt(LedgerNumber)


Dim ws As Worksheet
Dim tbl As ListObject
Dim rng As Range
Set ws = ActiveSheet
Set tbl = ws.ListObjects("Table" & X)
Set rng1 = Range("Table" & X).ListColumns(2)
Set rng2 = Range("Table" & X).ListColumns(3)


With tbl.Sort
        .SortFields.Clear
        .SortFields.Add Key:=rng1, Order:=xlAscending
        .SortFields.Add Key:=rng2, Order:=xlAscending
        .Header = xlYes
        .Apply
    End With

End If
 

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,534
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
You've already got a reference to the table, so you should use it:

VBA Code:
Set rng1 = tbl.ListColumns(2).Range
Set rng2 = tbl.ListColumns(3).Range
 

MeisterConrad

New Member
Joined
Jan 17, 2017
Messages
11
You've already got a reference to the table, so you should use it:

VBA Code:
Set rng1 = tbl.ListColumns(2).Range
Set rng2 = tbl.ListColumns(3).Range
Thanks so much for the help! I tried it out, and it worked – until it didn’t. The data-sort operation seems to take a long time to run. And, after the data-sort takes place, I get the error regarding:



Runtime error ‘-2147417848 (80010108)’:

Method ‘Apply’ of object ‘Sort’ failed



I’m not sure, but I wonder if the slow process has to do with the fact that the table being sorted has eleven columns, as if that’s a lot for this piece of code to run. I don’t know if any of that matters, but I do know that all eleven of those columns are staying in the table. So if the process takes so long, I wonder if it’s even worth correcting the ”Apply” error.

Rather than try to figure out what triggered that error, I thought maybe I’d try to recycle the some of my old code by modifying it to fit the new paradigm. This snippet of old code was repeated 19 different times, each time with select variables altered. It’s clumsy and redundant, but it did the job correctly. I’m just trying to make the coding a bit more “one-size-fits-all”, so it’s not so redundant. So I offer up this attached code, which first includes a sample of the old code for reference, and next includes the modified version of the old code.



So, I’m not sure if it’s easier to find and correct the “.Apply” error, or to use this alternative (attached) code, which has syntax issues with the IF-THEN statement’s last paragraph (I have range issues) .



Thoughts?







VBA Code:
‘ HERE IS THE OLD CODE THAT I MODIFIED TO GO WITH THE NEW CODE.



‘ If ActiveCell = Range("D9") Then

‘ ActiveWorkbook.Save

‘ Range("D12", Range("N" & Rows.Count).End(xlUp).Address).Sort Key1:=[E12], _

‘ Order1:=xlAscending, Header:=xlYes

‘ Range("D12", Range("N" & Rows.Count).End(xlUp).Address).Sort Key1:=[F12], _

‘ Order1:=xlAscending, Header:=xlYes

‘ Range("D11").Select

‘ Range("D" & Rows.Count).End(xlUp).Select

‘ End If





‘ HERE IS THE NEW STUFF – with its RANGE issues



Private Sub Worksheet_SelectionChange(ByVal Target As Range)



Dim LedgerNumber As String

Dim X As Integer

If InStr(ActiveCell.Text, "Table") > 0 Then

LedgerNumber = Mid(ActiveCell, 6, 2)

X = CInt(LedgerNumber)



Dim ws As Worksheet

Dim tbl As ListObject

Dim rng As Range

Set ws = ActiveSheet

Set tbl = ws.ListObjects("Table" & X)

Set rng1 = tbl.ListRows(1).ListColumns(1).Range

Set rng2 = tbl.ListRows(Rows.Count). ListColumns(11).Range

Set rng3 = tbl.ListRows(2).ListColumns(2).Range

Set rng4 = tbl. ListRows(2). ListColumns(3).Range

Set rng5 = tbl. ListRows(Rows.Count). ListColumns(1).Range



Range(rng1, rng2).End(xlUp).Address).Sort Key1:=[rng3], _

Order1:=xlAscending, Header:=xlYes

Range(rng1, rng2).End(xlUp).Address).Sort Key1:=[rng4], _

Order1:=xlAscending, Header:=xlYes

Range(rng1).Select

Range(rng5).End(xlUp).Select

ActiveWorkbook.Save



End If





End Sub
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,534
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
The sort doesn't actually happen until the Apply line runs. How many rows does your table have?

Is there any other code that might be interfering? What you posted is not a complete routine, so perhaps the issue is elsewhere.
 

MeisterConrad

New Member
Joined
Jan 17, 2017
Messages
11

ADVERTISEMENT

The sort doesn't actually happen until the Apply line runs. How many rows does your table have?

Is there any other code that might be interfering? What you posted is not a complete routine, so perhaps the issue is elsewhere.
Does the number of rows have to be defined? Because the table automatically adds rows when you just type in the cell. At any rate, in these tables, the number of rows grows over time (currently at 6 rows), and the data-sort operation should include all new rows as they are added to the table. Do I need a statement that tells Excel that bit of information?

And I must be understanding things wrong, because when I click to execute the code, the table flashes repeatedly really fast - like it's processing alot of data. The error message comes up after all the flashing goes on.

Thanks for being patient with mere mortals like me.
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,534
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
I was only asking because large tables can be slow in my experience, but 6 rows should be instantaneous. It sounds to me like you're creating some sort of loop. Can you post the full code that you are using?
 

MeisterConrad

New Member
Joined
Jan 17, 2017
Messages
11

ADVERTISEMENT

I was only asking because large tables can be slow in my experience, but 6 rows should be instantaneous. It sounds to me like you're creating some sort of loop. Can you post the full code that you are using?
My thought, too. Here's the whole thing:
Part 1 looks for the word "Table" in the active cell, copies the next 2 character in whatever text string that "Table" came from, and turn those two characters into an integer called "X". (This was my lesson in "InStr". Yay! a new thing.)
Part 2 Dims and sets the cascade of variables so that "Table" + X = the actual ListObject with that name. Also sets rng1 and rng2 for use in the data-sort operation.
Part 3 takes care of the data-sort operation. Now, I'm not familiar with ".Sort" and its syntax, so I wouldn't have thought it up. But I did see it and steal it and modify it from its raw form. And I can follow the logic of what the code looks to be doing. So, I don't understand what the flashing is about that looks the sheet refreshing itself over and over again.
Should I maybe move all my Dims/Sets to BEFORE the beginning of the If InStr?




VBA Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim LedgerNumber As String
Dim X As Integer
If InStr(ActiveCell.Text, "Table") > 0 Then
LedgerNumber = Mid(ActiveCell, 6, 2)
X = CInt(LedgerNumber)


Dim ws As Worksheet
Dim tbl As ListObject
Dim rng As Range
Set ws = ActiveSheet
Set tbl = ws.ListObjects("Table" & X)
Set rng1 = tbl.ListColumns(2).Range
Set rng2 = tbl.ListColumns(3).Range


With tbl.Sort
        .SortFields.Clear
        .SortFields.Add Key:=rng1, Order:=xlAscending
        .SortFields.Add Key:=rng2, Order:=xlAscending
        .Header = xlYes
        .Apply
    End With

End If


End Sub
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,534
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Perhaps try:

Code:
application.enableevents = false
With tbl.Sort
        .SortFields.Clear
        .SortFields.Add Key:=rng1, Order:=xlAscending
        .SortFields.Add Key:=rng2, Order:=xlAscending
        .Header = xlYes
        .Apply
    End With
Application.enableevents = true
 
Solution

MeisterConrad

New Member
Joined
Jan 17, 2017
Messages
11
Perhaps try:

Code:
application.enableevents = false
With tbl.Sort
        .SortFields.Clear
        .SortFields.Add Key:=rng1, Order:=xlAscending
        .SortFields.Add Key:=rng2, Order:=xlAscending
        .Header = xlYes
        .Apply
    End With
Application.enableevents = true
Holy crap! This works like a dream! What is this application.enable...? It looks like you're disabling some events, but only for the duration of the data-sort operation - like a bypass. What events are being disabled, and why does disabling them allow it to do what we want it to do? -That's like some Jedi/reverse psychology work!
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,534
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
I had a hunch that the sort was triggering the selection change code again, so you ended up in a recursive loop. Disabling events temporarily cures that problem.
 

Watch MrExcel Video

Forum statistics

Threads
1,128,128
Messages
5,628,864
Members
416,346
Latest member
Sekolaine

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top