Columns swap

[sapka]

Hi there,
I have this code to swap columns.

Sub test()
    Dim myrange As Range
 
    Set myrange = [A1:C5]
 
    myrange.Offset(, 10) = Application.Index(myrange, Evaluate("Row(" & myrange.Address & ")"), Array(3, 1, 2))
End Sub

It works well until I change myrange to something that starts not from the first row. Why is it happening this way? How to fix it?
Why if I change Evaluate part to myrange.Row it stops working correctly?

P.S. I am aware of other ways to swap columns, but this one for me would be the easiest.

 

[MickG]

Try :-

Sub test()
 Dim myrange As Range
 Set myrange = [A2:C5]
 myrange.Offset(, 10) = Application.Index(myrange, Evaluate("Row( 1:" & myrange.Rows.Count & " )"), Array(3, 1, 2))
End Sub

Mick

 

[sapka]

Brilliant – thanks! Can you explain why we use Evaluate here?

 

[Sandeep Warrier]

To answer your second question, you are evaluating the ROW() function. The parameter for the ROW() function can be either nothing... i.e. ROW() which returns the row number of the active row, or a range, for example ROW(A1), which returns the row number for the cell A1.

When you use myrange.row .... this will return the row number of the 1st cell in the range myrange. This value is an actual number and not a range. Hence, this will error out.

Hope this helps.

 

[sapka]

I can use then .Rows function (myrange.Rows), which will return range rather than number, but it still does not work with this function.

 

[MickG]

Have a look here:-
http://www.mrexcel.com/forum/showthread.php?p=2793030#post2793030
Mick

 

[MickG]

Have a play with this:-

[COLOR="Navy"]Sub[/COLOR] MG18Aug19
[COLOR="Navy"]Dim[/COLOR] ray, c, rng [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Set[/COLOR] rng = Range("A2:c5")
'[COLOR="Green"][B]If you Evaluate the "Rng" to return an array of the range rows.[/B][/COLOR]
'[COLOR="Green"][B]This will return "2,3,4,5", But what you want is an array "1,2,3,4"[/B][/COLOR]
'[COLOR="Green"][B]To achieve this ,if you offset the range by the First row in[/B][/COLOR]
'[COLOR="Green"][B]"Rng" row(2)+1 as below you will return the require array "1,2,3,4"[/B][/COLOR]
MsgBox Join(Application.Transpose(Evaluate("row(" & rng.Address & ")")))
MsgBox Join(Application.Transpose(Evaluate("row(" & rng.Offset(-rng(1).Row + 1).Address & ")")))
'[COLOR="Green"][B]Even if you reset the range to "A1:A5" you still get the right answer[/B][/COLOR]
[COLOR="Navy"]Set[/COLOR] rng = Range("A1:c5")
MsgBox Join(Application.Transpose(Evaluate("row(" & rng.Offset(-rng(1).Row + 1).Address & ")")))
[COLOR="Navy"]Set[/COLOR] rng = Range("A2:c5")
'[COLOR="Green"][B]Because Rng(1) row = 2 and 2-1 = 1, and rng offset(-1) = "A1:A4" = rows "1,2,3,4)[/B][/COLOR]
MsgBox rng(1).Row
MsgBox rng.Offset(-rng(1).Row + 1).Address
MsgBox Join(Application.Transpose(Evaluate("row(" & rng.Offset(-rng(1).Row + 1).Address & ")")))
 rng.Offset(, 5) = Application.Index(rng, Evaluate("Row(" & rng.Offset(-rng(1).Row + 1).Address & " )"), Array(3, 1, 2))
'[COLOR="Green"][B]Which all equals :-[/B][/COLOR]
MsgBox Join(Application.Transpose(Evaluate("row(1:" & rng.Rows.Count & ") ")))
rng.Offset(, 5) = Application.Index(rng, Evaluate("Row(1:" & rng.Rows.Count & " )"), Array(3, 1, 2))
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick