VBA assistance to modify code

Hello All,

I have a piece of code that almost works for what I need it to do.
Basically, I have data consisting of a variable number of column separated
strings. example

01AL,02AL,03AL,04AL

When I run the code below

Please Login or Register  to view this content.

It gives me sixteen rows of:

01AL
02AL
03AL
04AL
01AL
02AL
03AL
04AL
01AL
02AL
03AL
04AL
01AL
02AL
03AL
04AL

and a #VALUE down the rest of the column

I was hoping to only get four rows of:

01AL
02AL
03AL
04AL

with no #VALUE down rest of column.

Thank you in advance for any assistance/suggestions.


Moderator's Note: Please put code tags around codes, select the code then hit "#" sign. I'll do it for you, this time.

You need to resize the range you are putting the values in.

Try this.

Please Login or Register  to view this content.

one way:

Please Login or Register  to view this content.

Regards, TMS

Hello Norie,

That took care of the #VALUE, but still have sixteen rows of data with a new row inserted between each.
Thank you.

When I run the code I only end up with 4 rows of data.

That was with 01AL,02AL,03AL,04AL in A1, what data do you have exactly?

I'm not entirely sure what you are saying. This code lists 12 cells:

Please Login or Register  to view this content.

the number of commas is irrelevant.

Regards, TMS

Originally Posted by Norie

When I run the code I only end up with 4 rows of data.

That was with 01AL,02AL,03AL,04AL in A1, what data do you have exactly?

Hello Norie,

I can have a variable number of comma separators and a variable of data e.g. 01AL,02AL or AK05,AK06 in column B. I am on 2007 Excel. Thank you.

I realise you have multipe cells and multiple comma-separated values in the cells.

But what data do you have when the result is 16 rows of data with blanks inbetween?

Why don't we stop trying to second guess what your workbook, worksheet and data look like and you post a sample workbook with some typical data.

If you mock up what you want to see as output given specific input, I'm sure we can make it happen for you.

As it is, I'm losing interest ...

Regards, TMS

Hi,

Let me jump in here with an example of what I think you want and some code.

Here is how I'd do this problem. I took the column A and made what I think you want in Column B. I'd then delete column A to get exactly what I think you want.

Maybe this:

Please Login or Register  to view this content.

Regards, TMS

@MarvinP: I hadn't noticed a requirement to remove duplicates. But, if there is one, why do you need to sort the output?

Regards, TMS

You have the same list of four values in 5 rows, do you want one of the rows deleted?

Hi,

In his first post I found the result was filtered and sorted. I put filter and sort as separate subs so he could see them and the code for each sub function.

Marvin

First post?

Do you mean another thread?

Hi,

Run the macro with your name on it, with column J cleared of any data. I think this is what you want.

Marvin

That copies, sort and dedupes.

@Marvin: are you confusing this thread with something else you're working on?

There's only an excerpt of code without Code tags, no workbook and a very limited explanation of the requirement.

The first workbook example was in post #15


Regards, TMS

Just looked at the sample workbook. No way would I have guessed that the data looked like that, nor what was required.

All yours, guys.

TMS

Hey TM,

I wasn't confused. Sorry - but sometimes stuff just seems to make sense to me only.

Let me explain. I'm a For Next kind of guy. I saw his original code and saw what I thought was the problem. I didn't read his code and simply created my own sheet with his kind of data and wrote code to get to what I thought he wanted. I used Col A and B for my code. After he posted an example workbook it was easy to change the columns and run the code again.

I really don't understand some of these questions but I thought I did understand this one and it seemed to work for the OP.

Marvin

I'm confused.

Where did the OP mention putting the values in column J?

The original code was for column A and in the sample data it appears to be column B, no column J unless I missed it.

Final shot.

Please Login or Register  to view this content.