CODEWITHSUNDEEP
excelvba
Ahmed Mujtaba
Ahmed Mujtaba

Reputation: 2248

"Sort Method of Range Class Failed" -error

I have a spreadsheet that does not cooperate with a macro in our excel processor that we send these excel files through. The only way to get it to work is to copy the values only over to a new spreadsheet and adjust the date format manually. Only once it’s in these new spreadsheets can I send it through the excel processor with no hiccups.

I get "Sort Method of Range Class Failed" when trying to send the excel files through. I'm not much familiar with VB so I'm hoping someone can take a look at the bare-bone excel sheet data and tell me where the problem exists.

Here's a screenshot of the first few entries of the spreadsheet.

enter image description here

Update:

Upon debugging the error, the compiler stops at the below line:

Columns("A:H").Sort key1:=Range("H2"), order1:=xlAscending, Header:=xlYes

Here's the full code for this section only:

'Sort the rows based on the data in column C
Range("H1") = "Index"
Columns("A:H").Sort key1:=Range("H2"), order1:=xlAscending, Header:=xlYes
Application.ScreenUpdating = True
'Add big formula
Range("I2:I" & DataWBLRow) = BigConcat
Range("J2:J" & DataWBLRow) = FormString3

Upvotes: 0

Views: 3146

Answers (1)

Rik Sportel
Rik Sportel

Reputation: 2679

The problem occurs because in the sheet you're trying to sort the data is in a Table, a so-called ListObject. This much is also clear from your sample picture.

That's also why the code does work when you copy-paste the values to another, regular WorkSheet.

In short: Get rid of the Table formatting if altering the VBA is not an option for you. Otherwise, you'll have to test if the Range to sort is part of a ListObject or not and if so, then alter the code so that it does a sort on the ListObject.Range

Example:

Sub SortCols()
Dim ws As Worksheet
Dim lo As ListObject

Set ws = Worksheets("Sheet1")
Set lo = ws.ListObjects(1) 'My Table is range A1:H10

lo.Range.Sort key1:=Range("H2") 'Works perfectly
ws.Columns("A:H").Sort key1:=Range("H2"), order1:=xlAscending, Header:=xlYes 'Error as described in question.

End Sub

Upvotes: 3

Related Questions