CODEWITHSUNDEEP
excelvba
Mikey
Mikey

Reputation: 119

How to sort a selection

I'm trying to sort a selection. I've searched on google and all the hits want to sort the data in a selection, their headings are inaccurate. I want to sort the selection. So what's happening is my worksheet has lists of codes in columns. The user will select as many codes in as many columns as he desires (by using the control key and selecting cells wherever he wants). When I loop through the selection in VBA, I get the cells in the ORDER THAT THE USER SELECTED THEM. I need to process them in column order and then row order. My code that loops through the selection...

Dim cl As Range
For Each cl In Selection
    MsgBox "Found cell " & cl.Address & "..." & cl.Value
Next cl

My question is...How do I get my loop to give me the cells in order of (cl.column, cl.row)?

More information... I have pasted an image of my spreadsheet below. They are CPI codelists from the ABS Webservice API, although this is not important. I used the snipping tool, and when I took the image the highlighting of the selected cells didn't show, so I highlighted them again in yellow after I took the image. The cells could possibly have been selected by the user in any order, but I need to process the selected cells by (column, row). The selected cells are not necessarily contiguous.

My Spreadsheet

Upvotes: 0

Views: 90

Answers (2)

DisplayName
DisplayName

Reputation: 13386

I'm not aware of some "direct"/native method to loop through a range in a sorted row/column order

So here's a "indirect" one with SortedList object:

Option Explicit

Sub ListCellsSortedByColumns()
    Dim cell As Range
    Dim j As Long

    With CreateObject("System.Collections.SortedList")
        For Each cell In Selection
            .add GetKey(cell), cell
        Next

        For j = 0 To .Count - 1
            MsgBox "Found cell " & .GetByIndex(j).Address & "..." & .GetByIndex(j).Value
        Next
    End With
End Sub

Function GetKey(cell As Range) As String
    Dim var As Variant

    var = Split(cell.Address(, , xlR1C1), "C")
    GetKey = Format(var(1), "000") & "|" & Format(Mid(var(0), 2), "000")
End Function

where you should adapt the "000" part of the Format() functions to your case: "000" handles the sorting of cells with a maximum row/column index of 999

Upvotes: 1

Subodh Tiwari sktneer
Subodh Tiwari sktneer

Reputation: 9976

One way is this if the consecutive cells are selected.

Dim Rng         As Range
Dim r           As Long
Dim c           As Long
Dim StartRow    As Long
Dim EndRow      As Long
Dim StartCol    As Long
Dim EndCol      As Long

Set Rng = Selection

StartRow = Rng.Cells(1).Row
StartCol = Rng.Cells(1).Column

EndRow = StartRow + Rng.Rows.Count - 1
EndCol = StartCol + Rng.Columns.Count - 1

For c = StartCol To EndCol
    For r = StartRow To EndRow
        MsgBox "Found cell " & Cells(r, c).Address & "..." & Cells(r, c).Value
    Next r
Next c

Upvotes: 0

Related Questions