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.
Ex:
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
Our community of experts have been thoroughly vetted for their expertise and industry experience.
The Most Valuable Expert award recognizes technology experts who passionately share their knowledge with the community, demonstrate the core values of this platform, and go the extra mile in all aspects of their contributions. This award is based off of nominations by EE users and experts. Multiple MVEs may be awarded each year.
The Distinguished Expert awards are presented to the top veteran and rookie experts to earn the most points in the top 50 topics.