CODEWITHSUNDEEP
excelvbarange
Tomer W
Tomer W

Reputation: 3443

Excel Range sorts the with Cell order

as part of a project I did for a customer,
I let the user configure his 'important' range by specifying a Range-String (e.g. 'D5-F5, A5, G5, B4')
that tells me to copy the specified range to a summary page.

The following code is used to copy the "important range" to the summary page

rangeString = shtSettings.Cells(3, 2)
set importantRange = shtSource.Range(rangeString);
importantRange.Copy
shtSummary.Cells(<someLine>, 4).PasteSpecial xlPasteValues

I found out that Excel copies the cells in alphabetical order and not as specified by the rangeString
e.g. rangeString = 'A5, D5, B5' is treated as 'A5, B5, D5'
which is (understandably) counter-intuitive for the user.

Thanks

Upvotes: 0

Views: 44

Answers (1)

JvdV
JvdV

Reputation: 75860

Maybe something like:

Sub Test()

Dim Rng() As String, CopyRng As Range, RngStr As String, X As Long
RngStr = "A5,D5,B5"

With ThisWorkbook.Sheets("Sheet1")
    Rng = Split(RngStr, ",")
    For X = LBound(Rng) To UBound(Rng)
        Set CopyRng = .Range(Rng(X))

        Debug.Print CopyRng.Address 'Do Something With CopyRng

    Next X
End With

End Sub

Also I wouldn't want to let people create strings with a "-" in between cells.

Since you also ask for other idea's, what if you would not let users type a string, but let them actually pick? Is that an idea?

Sub Test()

Dim Rng() As String, PickedRng As Range, CopyRng As Range, X As Long

Set PickedRng = Application.InputBox(Prompt:="Select cells to copy", Type:=8)
With ThisWorkbook.Sheets("Sheet1")
    Rng = Split(PickedRng.Address, ",")
    For X = LBound(Rng) To UBound(Rng)
        Set CopyRng = .Range(Rng(X))
        Debug.Print CopyRng.Address 'Do Something With CopyRng
    Next X
End With

End Sub

This is not error proof but you get the idea. Some more info here

Upvotes: 2

Related Questions