Quick Array question - Copy array to another array then resize?

Hello all,

I am new to arrays and have a urgent need for the following scenario:

I have a variant 2 dimensional array (2 columns by X number of rows).

I need to somehow separate out the values from the first column of the array. (these values will be used to fill a listbox on a form - not that it matters).

Would it be possible to clone the array to a 2nd array and then resize the 2nd array so it only has the first column instead of both? (If anyone has a quicker/more efficient solution - I am open to suggestions).

I found CopyArraySubSetToArray on Chip Pearsons website http://www.cpearson.com/excel/vbaarrays.htm but I am uncertain as to how to use it and whether it is even what I need.

What do I need to enter for the the 3 long values?

Originally Posted by nilem

Or you can fill listbox by two-dimensional array, just set the width of the second column of listbox to 0.

Thanks nilem. I like the sound of your second suggestion. I am new to arrays. What code do I need to set the second column to 0?

(Below is the form sub I have currently if that is any help):

Please Login or Register  to view this content.

Hello nilem. It worked straight away. Thank you +1

Hello nilem. One minor problem. The code you provided in post #5 fills the Listbox1 but it doesn't fill it with a two dimensional array with the second column hidden (refer post #3)

Background information:
I need to read a 2 column range from a closed workbook. The listbox is filled with the second column of this range/array. I need to keep the original range/array data because I somehow need to return the value selected in the listbox to read the corresponding first column.

Originally Posted by nilem

Attach a small sample file with your form and listbox

Thanks nilem. OK.

Create a new XLSX file and put some dummy data into the first 4-5 rows of column A & column B. Name the sheet "employer10" and save and close the workbook. (This is a dummy workbook that will be used to test the code).

Now create a new XLSM.
Copy the code below into a new form. Add a Listbox and a Command button to the form. The Command button should be called OKButton.
You will need to add a public const for sServerFolderProgram & sServerFileDatabaseExcel. The first const should point to the XLSX folder location (without the last "\") and the second const should be the filename of the XLSX file.

Please Login or Register  to view this content.

Originally Posted by nilem

mc84excel,
Thanks for the detailed instructions
I found a suitable example, and hope this will help you

The example workbook worked. Thanks +1