1

I have this code to sort an Excel sheet

'for the ascending order
Range("A14:CB" & lastRow).Sort key1:=Range(Col & "14:" & Col & lastRow), Order1:=xlAscending, Header:=xlNo, key2:=Range("C14:C" & lastRow), Order2:=xlAscending, Header:=xlNo
'for the descending order
Range("A14:CB" & lastRow).Sort key1:=Range(Col & "14:" & Col & lastRow), Order1:=xlDescending, Header:=xlNo, key2:=Range("C14:C" & lastRow), Order2:=xlAscending, Header:=xlNo

This is what I get when I do ascending sort on Col=J

enter image description here

and this is what I get when I do descending sort on Col=J

enter image description here

Both are obviously wrong. Is there any error I am not seeing in the VBA code?

Community
  • 1
  • 1
L.Dutch
  • 926
  • 3
  • 17
  • 38

1 Answers1

1

Excel's sort works quite well, e.g. select a range and run the following:

Option Explicit

Sub TestMe()

    Selection.Sort key1:=Selection, Order1:=xlDescending

End Sub

In your code, you should check the variables, that define the range. E.g. lastRow and Col. Write something like this on the top of the code:

debug.print lastRow
debug.print Col
Vityata
  • 42,633
  • 8
  • 55
  • 100
  • `lastRow` is 17 (data begins at 14) and`Col` is 10 (when selecting column J) – L.Dutch Jan 18 '17 at 11:42
  • Then what do you expect from this thing here `Range(Col & "14:" & Col & lastRow)` E.g., if you go to the immediate window and you write `?Col & "14:" & Col & lastRow` what should it give you? I suppose that you have somewhere `On Error Resume Next`? – Vityata Jan 18 '17 at 11:51
  • `On Error Resume Next` has been removed, but still the issue is present. from the `Range(...)` I expect to act as `Range("J14:J17")` – L.Dutch Jan 18 '17 at 11:55
  • Writing what you asked in the immediate window returns `1014:1017` – L.Dutch Jan 18 '17 at 12:02
  • Then I suppose you can start fixing your error yourself. You should find a way to parse `Range(Col & "14:" & Col & lastRow)` to `Range("J14:J17")`. Here is how to start - http://stackoverflow.com/questions/12199318/vba-selecting-range-by-variables – Vityata Jan 18 '17 at 12:03
  • 1
    `Range("A14:CE" & lastRow).Sort key1:=Range(Cells(14, Col), Cells(lastRow, Col)), Order1:=xlAscending, Header:=xlNo, key2:=Range("C14:C" & lastRow), Order2:=xlAscending, Header:=xlNo` indeed solved the issue – L.Dutch Jan 18 '17 at 12:14