Splitting Cells and adding rows

[kasperblue]

Hello, I have a tricky problem and would love some advice please.

I have attached an example of my problem.

In the "before" sheet I have an example of my starting position and the "after" shows where I need to get.

Basically for every Article number (column A), I need to look through columns G:P in the same row and split out all the items (between either a | or [ ] )to make a new row with cloned cells in column A and B.

Lists in each cell may be extremely long and only one (or all cells) between column G:P in the same row may contain information.

The "after" version will end up with only information in columns A:G but will be significantly longer than the original.

Could anyone help me please?

Thanks
Kapserblue

[patel45]

try this

Please Login or Register  to view this content.

[kasperblue]

Hi Patel,

Thank you so much for your response. I having a couple of issue with the code, if you are able to help.

I've added in the variable definitions:

Dim sh1 As Range
Dim sh2 As Range
Dim destrow As Range
Dim LR As Long
Dim j As Long
Dim art As Long
Dim wl As Long
Dim descr As Long
Dim col As Long
Dim descr1 As Long
Dim descr2 As Long
Dim descr3 As Long
Dim arr As Long
Dim k As Long


I'm having an error with the line;

For k = 0 To UBound(arr)

It states "Compile Error: Expected array" - how do get around this please? I'm a bit confused.

Many thanks
Kasperblue

[patel45]

arr is an array, then Dim arr() As String

[patel45]

if you don't understand the code, it's better you don't add definitions or define all variables as Variant

Please Login or Register  to view this content.

[kasperblue]

Thanks so much, this does work now.

In column "C" both the descriptor and the number are in the same cell. i.e ELEPHANT 11 42 1 250 534.

Could ELEPHANT be in column C and 11 42 1 250 534 in column D?

[patel45]

Please Login or Register  to view this content.