CODEWITHSUNDEEP
sortingexcelvba
baarkerlounger
baarkerlounger

Reputation: 1226

Excel VBA Sorting a named range by a particular column

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

Upvotes: 2

Views: 18472

Answers (1)

user2140173
user2140173

Reputation:

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 :)

Upvotes: 4

Related Questions