Excel VBA Sorting a named range by a particular column

Question

I have a selected range and within that Range I would like to sort by a particular column so that each row is kept the same i.e. the sort function just reorders the entire rows based on whichever column I have selected to sort by.

My code so far is this:

Sub CustomSortAscendingExcel(ByVal control As IRibbonControl, ByRef cancelDefault As Variant)
Dim selRange As range
Dim usedRange As range
Dim checkRange As range
Dim shtData As Worksheet
Set shtData = Worksheets("Data")
shtData.Activate
shtData.Unprotect
Set selRange = Selection
Set usedRange = returnUsedRange
Set checkRange = Intersect(usedRange, selRange)
If Not Application.Intersect(usedRange, selRange) Is Nothing Then
With ActiveSheet
    usedRange.Sort Key1:=selRange, Order1:=xlAscending, DataOption1:=xlSortNormal, MatchCase:=False, SortMethod:=xlPinYin, _
    Header:=xlNo, Orientation:=xlSortColumns
    .Sort.Apply
End With

And similar for sorting Descending. This works fine for column1. My problem is that for columns 2, 3, 4 it only works correctly for those rows that have a value in column1 not for any that don't. For example in the image below sorting by column 3 correctly reorders the first 9 rows but not the last two.

Can anyone suggest a way to fix this? Thanks!

enter image description here

usedRange here refers to the whole table (A:2, D:11), selRange refers to the column I've selected to sort by — baarkerlounger
– baarkerlounger, Commented May 2, 2013 at 10:44

user2140173 · Accepted Answer · 2013-05-02 10:44:01Z

4

I recorded a simple macro that selects Range("A1:D5") , applies filter, then sorts in ascending order based on the 3rd column (C)

look at this:

Range("A1:D5").Select
    Selection.AutoFilter
    ActiveWorkbook.Worksheets("Sheet1").AutoFilter.Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").AutoFilter.Sort.SortFields.Add Key:=Range _
        ("C1:C5"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

Now, there isnt much you have to do to fix your problem :)

answered May 2, 2013 at 10:44

user2140173

Sign up to request clarification or add additional context in comments.

4 Comments

baarkerlounger Over a year ago

Could you explain please what the AutoFilter lines are doing? Thanks

baarkerlounger Over a year ago

I get a 'Subscript out of range error' On the 3rd line? I'm also still not sure what the filtering does since it doesn't hide anything - sorry if I'm being unusually slow here!

user2140173 Over a year ago

debug.print and check the value of highlighted line in yellow. mouse over and see why youre out of range. You apply autofilter to be able to sort by header criteria. it has an additional/another functionality to hide ( filter ) rows but its not what you needed according to your question.

baarkerlounger Over a year ago

It turns out my original code worked - the problem was in how I calculated the last used row in my usedRange. I'm really sorry to have wasted your time and appreciate the help - since this solution also works I will mark it solved

Collectives™ on Stack Overflow

Excel VBA Sorting a named range by a particular column

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related