VBA Collect Values Stored in Merged Cells into Array

Hello All,

I am a newbie to vba and the forum and hope to learn lots of new things from here.

My first post is related to the collection of string values (which are names) from merged cells into an array which I wish to then pass on to another sheet.

I have a sheet named "Cashbook" with the names commencing at row 20 of columns K:L (which are merged cells). This pattern is consistent across the entire sheet.

I am wanting to collect the values in each of the cells from the start point to the end noting that the array will need to be dynamic as it grows as I enter data (currently 62 records / 124 columns). After doing so, I then want to transfer the data from the array into A2 on another sheet named "Selects"

The only code I have so far is as follows:

Please Login or Register  to view this content.

I am wondering if someone can teach me how to achieve this?

Thanks in advance.

Hello fiasco,

Welcome to the Forum!

This code example finds the number of rows used and copies the values to the other sheet. The code includes a method to list vertically (down the rows) and horizontally (across the columns).

Please Login or Register  to view this content.

Hi Leith.

Thanks for the welcome plus the help on my question.

In reviewing the answer you provided, I found that the code to be specific to rows whereas my question related to columns with merged cells.

After doing some further research on Google, I have modified the code you provided (below) but cannot seem to get it to work the way I need.

Please Login or Register  to view this content.

If you could take another look at it I would be very grateful.

Thanks

Hello fiasco,

Sorry, I am not following your logic. If you have a workbook you can post, it will help me follow what you want to do.

How To Post Your Workbook

1. At the bottom right of the Reply window, Click the button Go Advanced
2. At the top of the Your Message Window, in the first row of icons, Click the Paperclip icon.
3. Click the Add Files button at the top right in the dialog box. This displays the File Manager dialog.
4. Click the Select Files button at the bottom middle of the dialog.
5. In the new window Find the file you want to upload, select it, and Click Open.
6. You will now be back in the File Manager dialog. Click the bottom Middle button Upload File.
7. Wait until the file has completely uploaded before you exit the File Manager dialog.

Hello fiasco,

This macro worked for me based on your sample . I have added it to the attached workbook.

Please Login or Register  to view this content.

Hello fiasco,

You're welcome. Couldn't have done it without the workbook. thanks for posting it.