New to arrays-Can I move a column in a 2D array without looping to add data?

OK this is probably a dumb question. But I am still learning VBA arrays.

Suppose I have a 2D array and I want to do the VBA equivalent of taking an Excel range and Cut Column, Insert Column. (So say I had 4 columns in the array. I want to alter the array so that the 3rd & 2nd column switches places e.g. before = ABCD, after = ACBD).

Is this possible? (Short of building a copy of the array and looping through columns).




Additional Information:

• Please don't say "just cut & paste the columns in the original range before creating the array". That's not what the thread is asking!
• For forum users who like to know a practical use for thread before they offer a reply - I am using the array to build a listbox. I want the 2nd & 3rd columns switched in the listbox. I figured that it would be easier to do this in the array rather than populate the listbox and then try to switch columns. (Note I don't know how many columns will be in the array. But I do know that the 3rd & 2nd have to be switched).

Thanks kalak. However I am trying to switch the "column" order in the vba array. (Not on an excel worksheet).

There's no easy way to switch 'columns' in an array. (like cut-and-paste, I mean.)

I think you should do the swap in a loop at userform initialization.

Please Login or Register  to view this content.

Thanks Tinbendr +1

Originally Posted by Tinbendr

I think you should do the swap in a loop at userform initialization.

Please Login or Register  to view this content.

This code looks good but I have 2 questions:

1. I had a ListBox and you have a ComboBox. Can I still use a ListBox? (haven't learnt ComboBox yet...)
2. As I said in the opening post, I won't know how many columns I will have each time. How would I edit your code to to have variable column numbers?

Yes. Listbox and comboboxes are mostly interchangeable.

You can do something like this, but how will you know which column to switch?

Please Login or Register  to view this content.

Also, you are limited to 10 columns in both controls.

I reckon you've already had the answer to this-for instance in your post about populating a sheet with two columns from an array. hint-the Array statement for the third argument of application.index can take the columns in any order you want
if you are populating the array from a sheet you can populate it in any order you like using the same technique :-)

Originally Posted by JosephP

if you are populating the array from a sheet you can populate it in any order you like using the same technique :-)

Slaps head. Of course! Good point