Descend Macro not working properly

[Livin404]

Good evening,

For the life of my I cannot figure out why my "Descend" Macro is not working properly. I checked to make sure my database name is correct, and the Columns are correct. This code works for multiple other databases, but for a strange reason is not here.

The error message I get is "Sort method of Range class failed". When the debug button is pressed it highlights

.Columns("A:K").Sort key1:=.Range("A2"), order1:=xlDescending, Header:=xlYes

The macro I'm trying to use is:

Private Sub CmdDescend_Click()
Dim xlSort As XlSortOrder
Dim LastRow As Long
With Sheets("ACTIVITY")
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
.Range("A1") = "Date"
.Columns("A:K").Sort key1:=.Range("A2"), order1:=xlDescending, Header:=xlYes
Call Submit
Call RESET
End With
MsgBox "Select Refresh Tab.", vbOKOnly + vbInformation, "Descend"
End Sub

Thank you,

 

[Alex Blakenburg]

It works fine when I run it, which makes a data issue the most likely.
Can you provide an XL2BB sample of your data and tell us what it is doing or not doing ?
Note: The sample needs to be a realistic representation of the Data so we can see if there are data type issues.
Also do you have rows that look blank underneath that actually include formulas. Also do you have any fully blank rows inside what you consider to be the data set.

 

[Fluff]

Is your data in a structured table, or just a normal range?

 

[Livin404]

Is your data in a structured table, or just a normal range?

It is a normal range. There is data there, I made sure the date was in the correct format. Column K is just the day of the month, I have it for my slicer. I don't think my macro is incorrect maybe it's based on the database, but I can't see how I can adjust for that. My data source is "ACTIVITY!$A:$K".

Thank you so much.

 

[Livin404]

It is a normal range. There is data there, I made sure the date was in the correct format. Column K is just the day of the month, I have it for my slicer. I don't think my macro is incorrect maybe it's based on the database, but I can't see how I can adjust for that. My data source is "ACTIVITY!$A:$K".

Thank you so much.

 

[Fluff]

It is a normal range.

In that case your code works fine for me. It only only fails if you are using it on a table.

 

[Alex Blakenburg]

It is a normal range. There is data there, I made sure the date was in the correct format. Column K is just the day of the month, I have it for my slicer. I don't think my macro is incorrect maybe it's based on the database, but I can't see how I can adjust for that. My data source is "ACTIVITY!$A:$K".

Thank you so much.

View attachment 75313

So if you are in a cell in the table, you don't see the "Table Design" Tab right ?
because the look of what you are showing us is very much like what is generated when you convert to a Table.

If definitely not a table we are still back to that it is most likely a data issue.
1) populating A1 = "Date" worries me since it indicates Column A might be empty at this point.
2) What does "is not working properly" actually mean. What is it or is it not doing ?
3) We could still use and XL2BB of sample of your data set (realistic data that retains the data type characteristics of the data)
4) If you set a breakpoint at Call Submit and look at the data has it definitely not sorted (ie are the Called subroutines changing the sort)

 

[Livin404]

In that case your code works fine for me. It only only fails if you are using it on a table.

You were right. I unwittingly created a table, so I just adjusted the macro to. It seems to be fine now. Thank you,

Private Sub CmdDescend_Click()
Dim tbl As ListObject
    Dim col As Range
    Set tbl = Worksheets("ACTIVITY").ListObjects("Cargo_Moved")
    Set col = tbl.ListColumns("Date").Range
    With tbl.Sort
        .SortFields.Clear
        .SortFields.Add Key:=col, Order:=xlDescending
        .Header = xlYes
        .Apply
    End With
    MsgBox "Select Refresh Tab.", vbOKOnly + vbInformation, "Descend"
End Sub

 

[Fluff]

Glad you sorted it & thanks for the feedback.