VBA Sort Not Working

[katk]

Hi all,

As part of a much larger code, I have the following, which is supposed to sort a worksheet based on the values in columns F and G which are text like "CALIFORNIA" and "KINGS RIDGE". The code runs through without errors, but the data doesn't actually get sorted (any of it). At one point, I thought it was because there was a leading space (eg " CALIFORNIA") but I set up an LTrim before the sort to get rid of the spaces and it's still not working, so now I'm just confused. I don't know if there's a way to specify "A to Z" as the Sort order in VBA but I think I've sorted text using Ascending before and it's worked... Any help on figuring out why my Sort is failing would be greatly appreciated. Thanks!

Cells.Select
    ActiveWorkbook.Worksheets("Item Setup").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Item Setup").Sort.SortFields.Add Key:=Range( _
        "F1:F1216"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    ActiveWorkbook.Worksheets("Item Setup").Sort.SortFields.Add Key:=Range( _
        "G1:G1216"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
     With ActiveWorkbook.Worksheets("Item Setup").Sort
        .SetRange Range("A1:J2692")
        .Header = xlYes
    End With

 

[VoG]

I think that you are missing the line in red

     With ActiveWorkbook.Worksheets("Item Setup").Sort
        .SetRange Range("A1:J2692")
        .Header = xlYes
        .Apply
    End With

If that doesn't fix it, try recording a macro whilst sorting manually to get the correct syntax.

 

[Smitty]

All you've done is celared/set the Sort properties, but not to actually sort.

Try adding .Apply to the end of your with statement.

You might also consider making the sort ranges dynamic.

HTH,

 

[katk]

Ah, thanks! That worked! That line must have gotten lost somehow while I was combining bits of code...