How to convert numeric data in an excel column into a string of comma values.

Hi Desgordon,

Please be aware that a cell (in Excel 2003) can only hold 32,767 characters and will only display about 1000. (http://office.microsoft.com/en-us/ex...005199291.aspx) It may be an issue if you have "so many rows" that it fills the cell to the limit.

For less than the limit, you could use something like the following - assuming your data was in column A:

Please Login or Register  to view this content.

An alternative to looping through possibly thousands of rows might be to dump the range into an array and process it that way, then spit out the result as one string. (Same result, just different method.)

Please Login or Register  to view this content.

The first code snippet shouldn't duplicate anything. It should work the same way as the second, just a different method as stated. (Same results in my book.) In order to skip the hidden rows, you'll likely have to use the first option with a slight adjustment. I suppose you could build an extra array into the second option that contains a 1 if a row is hidden, 0 if not, but then you'd still be looping through the rows anyway.

Please Login or Register  to view this content.

Hi Paul,

You're right about the duplication bit. The problem is that the function is reading from Column A which has duplicated values and is not the column I want, so I made the modifications shown below hoping that it would read from Column B instead:

Please Login or Register  to view this content.

But its still reading from Column A. Any ideas why.

Yes, because "Cells(i, 1)" refers to the cell in row "i", column 1 (which is "A"). Change any instances of Cells(i, 1) to Cells(i, 2) and you should be set.

Also, please be sure to wrap your code within [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags in the future, per the forum rules. I've done it for you this time.

Thanks!

Note that there were two instances of "Cells(i, 1)" in the last code I provided which needed to be changed. If you didn't change the second one, it would still have been grabbing values from column 1 (A). Hopefully that's it sorted now.

You would put that code into the worksheet module that contains the data you want modified.

Right-click on the sheet tab that has your data and choose View Code. Copy the code above and paste it into the VB Editor window that appears. Close the VB Editor. Run the macro by pressing ALT+F8 to open the Macros dialog.

Note that the code is specific to cells and columns that the original poster requested. If your needs are different please start your own thread. You can reference this thread via hyperlink if necessary.