Text to rows and copy rows.

[interner]

I'm not completely sure if I am dreaming too big here, so please let me know if my idea is a bit ludiculous.

I have a spreadsheet that contains two columns (columns "O" and "P") with serial numbers in them. The serial numbers in these two columns are separated by commas.

I wrote some script to calculate how many commas are in these last two columns. I stored this number in a variable named 'commas' as such.

Lastcell = Cells(Rows.Count, "O").End(xlUp).Row
For i = Lastcell To 1 Step -1
If (Cells(i, "O").Value) <> "" And Rows(i).EntireRow.Hidden = False_ Then
commas = Len(Cells(i, "O").Value) - Len(Replace(Cells(i, "O").Value, ",", ""))

So, I have my 'commas' variable. What I would like to do is perform a "text to rows" sorta deal. I'm trying to give each serial number its own unique row but have it contain the same information as the columns that come before it. The comma would be used as my delimiter in this particular case.

This process needs to be performed for column "O" first then column "P."

So the gist of it, each serial number (in column "O") gets its own row, and the information stored in columns 'A' through 'N' is right there with it. Then this process needs repeated for column 'P' whilst preserving the the information in columns 'A' through 'N' again.

Needless to say, any help would be greatly appreciated. If any clarification is needed please say so.

[6StringJazzer]

Here's a quick take, if it's still not clear then I might be able to find time to do more.

First, forum rules require code to be enclosed in "code" tags. Please see my signature below for instructions.

Look up the function SPLIT. It will take the list of serial numbers and put each serial number into an array element. Then you repeatable can copy and insert the row, looping through the array to insert the next serial number.

Your description says there are two columns with serial numbers, but your code only shows one column, so I'm not quite sure how you're handling that.

BTW that's a clever way to count the commas.