VBA Macro to insert rows when the following condition is met

ericcarl

New Member
Joined
Apr 7, 2016
Messages
7
Hello Forum,
I'm trying to write a conditional macro where VBA will add (x) number of rows depending on how many separate numbers appear in a specific cell. However, I just discovered VBA and I can't find any postings that specifically address this scenario. I don't know if it can be done with a formula - I figured only VBA could add rows dynamically. I've included a generic and fictional example below of how the data appears and how it needs to look like. I am using Excel 2010.

Important Note: Ideally, all of the customer data that appears in columns A - L and N - onward should all replicate on the rows that are created beneath the first row. Also, all of the account number values will be delimited by a comma that separates them and some of them will vary slightly in length.

CURRENT FORM:

Column A
....
....
Column M
Customer_Name
Account_Number
Customer 1
1111111111,2222222222,333333333,44444444444,5555555555,6666666666,7777777777

<tbody>
</tbody>







DESIRED FORM:

Column A
....
....
Column M
Customer_Name
Account_Number
Customer 1
1111111111
Customer 1
2222222222
Customer 1
333333333
Customer 1
44444444444
Customer 1
5555555555
Customer 1
6666666666
Customer 1
7777777777

<tbody>
</tbody>















I have no idea if this can be done but I would be eternally grateful if so :)

Thank you in advance for your help!
~Eric
 

Some videos you may like

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

CalcSux78

Well-known Member
Joined
Oct 15, 2013
Messages
1,120
Eric,

You could use the SPLIT function to split the value in column M into an array then insert required rows below that row. Let me know if I lose you on this.

Code:
Sub FlexIt()
Dim R&  [COLOR=#008000]'as long[/COLOR]
Dim n&
Dim lRow&          [COLOR=#008000]   'last row#[/COLOR]
Dim Arr As Variant  [COLOR=#008000]'Array[/COLOR]
Dim sCust$          [COLOR=#008000]'as string[/COLOR]


lRow = Cells(Rows.Count, 1).End(xlUp).Row
Application.ScreenUpdating = False
[COLOR=#008000]'loop backwards since inserting rows[/COLOR]
For R = lRow To 2 Step -1
    sCust = Cells(R, 1)
    Arr = Split(Cells(R, 13), ",")     [COLOR=#008000] 'split column M into array[/COLOR]
    Cells(R, 13) = Arr(0)
    For n = 1 To UBound(Arr)
        Rows(R + n).Insert xlDown
        Cells(R + n, 1) = sCust
        Cells(R + n, 13) = Arr(n)
    Next n
Next R
Application.ScreenUpdating = True
End Sub
 

ericcarl

New Member
Joined
Apr 7, 2016
Messages
7
CalcSux78,
Sorry for my delayed response - thank you very much for your help. That was very helpful. :)

Happy Holidays!
ericcarl
 

Watch MrExcel Video

Forum statistics

Threads
1,123,158
Messages
5,600,052
Members
414,357
Latest member
Gemma_R

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top