VBA - Duplicate rows where celles have values seperated by a comma
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 VariantmyCols = InputBox("Duplicate what columns?" & vbCrLf & "For multiple columns, please separate column letters by a comma.")With Sheets("Result") .UsedRange.Cells.Offset(1, 0).ClearContentsEnd WithEnd 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.
Open in new window