troubleshooting Question

VBA - Duplicate rows where celles have values seperated by a comma

Avatar of Wilder1626
Wilder1626Flag for Canada asked on
Microsoft OfficeVBAMicrosoft Excel
3 Comments1 Solution11 ViewsLast Modified:
Hi, everyone
I need you help with a macro I would need to build for an excel file.
In my sheet called “Master”, I have multiple columns that could vary. Could be less or could be more.
The first row would always be the column header names.
Then I can have up to 3000 rows of data +/-.
The objective of the macro is to duplicate rows when a cell from specified columns contain multiple values separated by a comma.
The columns would be identified in a Message Input Box where Column Letters would be separated by a comma as well if more than one as you will see in below picture. So columns to search those values with comma may vary.
To add to the complexity, it I select 3 columns that could contain values with comma, it would duplicate the rows making sure that I have a single sample of each values.
 Private Sub DupRows_Click()
Dim myCols As Variant
myCols = InputBox("Duplicate what columns?" & vbCrLf & "For multiple columns, please separate column letters by a comma.")

With Sheets("Result")
        .UsedRange.Cells.Offset(1, 0).ClearContents
End With

End Sub

As you can see in above picture row 2, Column F and M as values separated with Comma. It have 4 different values per cells.
Once the macro runs, it would create 16 identical rows but only put unique values from the strings with comma.
For row 3,  in the 3 selected columns, only column C as values separated by comma. It would then duplicate 4 rows but only putting 1 single value per rows.

The result would then be like below:
The data should be pasted to the sheet called: “Result”. It should not change anything in the sheet called “Master”

I also need to say that the number of values separated by a comma can also vary.
How can I do that?
Thank you for your help.

 Duplicate rows no1.xlsm

Join our community to see this answer!
Unlock 1 Answer and 3 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 3 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros