Strange Error in Dynamic Sorting

[Manan]

Hi All,

I am getting the strange error in executing the below code. Can Someone help.

  Sheets("Data").Sort.SortFields.Clear
  Sheets("Data").Sort.SortFields.Add Key:=Range("A9:H" & srtRng), _
  SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
  With Sheets("Data").Sort
  .SetRange Range("A8:H" & srtRng)
  .Header = xlYes
  .MatchCase = False
  .Orientation = xlTopToBottom
  .SortMethod = xlPinYin
  .Apply
  End With

Error Coming on .Apply Statement : Runtime Error 1004.

 

[NARAYANK991]

Hi Manan ,

Try the following :

    Sheets("Data").Sort.SortFields.Clear
    Sheets("Data").Sort.SortFields.Add Key:=Range("A9:A" & srtRng), _
                                      SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With Sheets("Data").Sort
        .SetRange Range("A8:H" & srtRng)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
  End With

I do not know whether srtRng refers to a number giving the last row of data or it refers to a Range ; always use meaningful names for variables. Rng is normally used to refer to a range. lastrow is normally used to refer to the last row of data , and will generally be declared as a variable of type Long.

The key for sorting cannot be a multiple column range ; I have changed it to column A ; you need to change it to what ever is relevant for your purpose.

Narayan