Revise macro that expands data across multiple rows

Hi everyone,

I have a spreadsheet with rows of data. Each row represents a separate product and column G contains all the colours for the product in each row. Each of the colours is separated by the pipe symbol. I need a macro to look at column G in each row and then create copies of each row depending on how many colours there are for the product.

I have attached an excel file with sample data. Sheet 1 contains 2 rows (excluding the header row). The first row contains 4 colours in Column G and the second row contains 3 colours in Column G. If you look at Sheet 2, you can see that there are now 7 rows, ie. 4 for the first SKU (in sheet 1) and 3 for the second SKU (in sheet 1) and each of the colours from column G is now separated out onto its own row.

I have the following VBA script that works if the colours are in column B but because mine are in column G it doesn't seem to work:

Sub macro()
For Idx = Range("A" & Rows.Count).End(xlUp).Row To 2 Step -1
    Set c = Cells(Idx, 2)
    If InStr(1, c, "|") > 0 Then
        Colors = Split(c, "|")
        NrColors = UBound(Colors)
        c.EntireRow.Copy
        Rows(c.Row + 1 & ":" & c.Row + NrColors).Insert Shift:=xlDown
        For Idx1 = 0 To NrColors
            c.Offset(Idx1) = Colors(Idx1)
        Next
    End If
Next
End Sub

Open in new window


I wondered if someone could help me revise the above script so that it works with my spreadsheet configuration, ie. where the colours are in column G?

Would really appreciate any help.

Thanks in advance.
test-data3.xlsx
LVL 1
gwh2Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

[ fanpages ]IT Services ConsultantCommented:
Hi,

A change is required on this line:

Set c = Cells(Idx, 2)

2 indicates the second column, i.e. [ B ].

For column [G], you can use:

Set c = Cells(Idx, 7)

Alternatively, if this is easier to read:

Set c = Cells(Idx, "G")

Sub macro()

  Dim c                                                 As Range
  Dim Colors                                            As Variant
  Dim Idx                                               As Long
  Dim Idx1                                              As Long
  Dim NrColors                                          As Long
  
For Idx = Range("A" & Rows.Count).End(xlUp).Row To 2 Step -1
    Set c = Cells(Idx, "G")
    If InStr(1, c, "|") > 0 Then
        Colors = Split(c, "|")
        NrColors = UBound(Colors)
        c.EntireRow.Copy
        Rows(c.Row + 1 & ":" & c.Row + NrColors).Insert Shift:=xlDown
        For Idx1 = 0 To NrColors
            c.Offset(Idx1) = Colors(Idx1)
        Next
    End If
Next

End Sub

Open in new window

0
[ fanpages ]IT Services ConsultantCommented:
Kanti Prasad - Expert Comment 2015-07-26 at 11:12:26 ID: 40899023
---
Hi

Try this

Sub macro()
For Idx = Range("A" & Rows.Count).End(xlUp).Row To 2 Step -1
    Set g = Cells(Idx, 2)
    If InStr(1, g, "|") > 0 Then
        Colors = Split(g, "|")
        NrColors = UBound(Colors)
        g.EntireRow.Copy
        Rows(g.Row + 1 & ":" & g.Row + NrColors).Insert Shift:=xlDown
        For Idx1 = 0 To NrColors
            g.Offset(Idx1) = Colors(Idx1)
        Next
    End If
Next
End Sub

---

I think you may have misunderstood the requirements of the question, Kanti Prasad.

Simply renaming the variable 'c' to 'g' is going to produce the same result (that being an interrogation of column [ B ], not any action on column [G] as requested).
0
Kanti PrasadCommented:
Hi

Try this you just need to change the c to g and reference g at 7 instead of  b at 2

Sub macro()
For Idx = Range("A" & Rows.Count).End(xlUp).Row To 2 Step -1
    Set g = Cells(Idx, 7)
    If InStr(1, g, "|") > 0 Then
        Colors = Split(g, "|")
        NrColors = UBound(Colors)
        g.EntireRow.Copy
        Rows(g.Row + 1 & ":" & g.Row + NrColors).Insert Shift:=xlDown
        For Idx1 = 0 To NrColors
            g.Offset(Idx1) = Colors(Idx1)
        Next
    End If
Next
End Sub

Open in new window

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

gwh2Author Commented:
Thanks so much for that. I made the change and it's all working well now which is great. Could I ask just one further related question: I need to paste in data from another excel spreadsheet into the spreadsheet with the macro. To do this, I need to create a new worksheet but when I do that the macro is not within that new worksheet, ie. I have to continually go into the editor and paste the code into the new worksheet in order to run the macro.

Is there a way to have the code automatically popular each new worksheet that I create?
0
[ fanpages ]IT Services ConsultantCommented:
"Try this you just need to change the c to g and reference g at 7 instead of  b at 2"

Sorry, but the name of the variable 'c' does not need to be changed at all.
0
gwh2Author Commented:
I just changed this:

Set c = Cells(Idx, 2)

to this as you suggested:

Set c = Cells(Idx, 7)
0
[ fanpages ]IT Services ConsultantCommented:
@gwh2:
---
Thanks so much for that. I made the change and it's all working well now which is great. Could I ask just one further related question: I need to paste in data from another excel spreadsheet into the spreadsheet with the macro. To do this, I need to create a new worksheet but when I do that the macro is not within that new worksheet, ie. I have to continually go into the editor and paste the code into the new worksheet in order to run the macro.

Is there a way to have the code automatically popular each new worksheet that I create?
---

You're very welcome.

If you would like the code to be available in every workbook, then you need to consider adding it to a "personal" workbook.

Here is a previous discussion about that approach:
[ http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_28096747.html ]

Alternatively, place the code in a "blank" workbook; one with just the code, & save this as a known filename.

Whenever you wish to create a new workbook file, simply copy this workbook, rename it to your chosen new filename, & continue as normal (retaining the original workbook with the previous code, & the new workbook on which you will then build more content with a different filename).
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Kanti PrasadCommented:
Hi

Sorry I acutally deleted my  1st comment, but somehow it stayed. The above one it now tested.
0
[ fanpages ]IT Services ConsultantCommented:
@gwh2:
---
I just changed this:

Set c = Cells(Idx, 2)

to this as you suggested:

Set c = Cells(Idx, 7)
---

Yes, that's fine.

My other responses (apart from the one replying to your subsequent question) were to Kanti Prasad.
0
gwh2Author Commented:
@Kanti Prasad - thank you for your input but the first response has solved my problem.

@[ fanpages ] - thanks again for the additional information.
1
[ fanpages ]IT Services ConsultantCommented:
No problem at all.

Thanks for closing the question so promptly.
0
Kanti PrasadCommented:
Hi gwh2

no problem but just change that c  to g as a matter of good practice as you don't refer column g with a c which also avoids confusion.

Set c = Cells(Idx, 2)  to Set g = Cells(Idx, 2) and change all the c. to g.
0
[ fanpages ]IT Services ConsultantCommented:
Kanti Prasad - Expert Comment 2015-07-26 at 11:41:15 ID: 40899041
---
no problem but just change that c  to g as a matter of good practice as you don't refer column g with a c which also avoids confusion.

Set c = Cells(Idx, 2)  to Set g = Cells(Idx, 2) and change all the c. to g.
---

^ I presume the original author of the code used 'c' simply as an abbreviation of 'cell(s)'.

Are you suggesting the author should have used 'b' (as column [ B ] was the original column being interrogated)?
0
Kanti PrasadCommented:
Hi

I suggested using g just to remove confusion and to show it picks values from column g as it is best to declare variables meaningfully which is a good practice!

Also as gwh2 mentioned it is changing the B pipes correctly so the author is aware that  Set c = Cells(Idx, 2) means b values.
0
[ fanpages ]IT Services ConsultantCommented:
Kanti Prasad - Expert Comment 2015-07-26 at 12:00:09 ID: 40899046
---
I suggested using g just to remove confusion and to show it picks values from column g as it is best to declare variables meaningfully which is a good practice!

Also as gwh2 mentioned it is changing the B pipes correctly so the author is aware that  Set c = Cells(Idx, 2) means b values.
---

With respect, that is nonsense.

Yes, naming variables to avoid confusion is good practice.

Your justification for misunderstanding the question is making your potential solution confusing.
0
Kanti PrasadCommented:
0
[ fanpages ]IT Services ConsultantCommented:
Kanti Prasad - Expert Comment 2015-07-26 at 12:39:51 ID: 40899058
---
Like wise with respect refer point 3

http://codebuild.blogspot.ie/2012/02/15-best-practices-of-variable-method.html
---

(To avoid your comment being removed, I have transposed the content of the page below)

---
15 Best Practices of Variable & Method Naming

1.      Use short enough and long enough variable names in each scope of code. Generally length may be 1 char for loop counters, 1 word for condition/loop variables, 1-2 words for methods, 2-3 words for classes, 3-4 words for globals.
2.      Use specific names for variables, for example "value", "equals", "data", ... are not valid names for any case.
3.      Use meaningful names for variables. Variable name must define the exact explanation of its content.
4.      Don't start variables with o_, obj_, m_ etc. A variable does not need tags stating that it is a variable.
5.      Obey company naming standards and write variable names consistently in application: e.g. txtUserName, lblUserName, cmbSchoolType, ... Otherwise readability will reduce and find/replace tools will be unusable.
6.      Obey programming language standards and don't use lowercase/uppercase characters inconsistently: e.g. userName, UserName, USER_NAME, m_userName, username, ...
o      For example for Java,
      use Camel Case (aka Upper Camel Case) for classes: VelocityResponseWriter
      use Lower Case for packages: com.company.project.ui
      use Mixed Case (aka Lower Camel Case) for variables: studentName
      use Upper Case for constants : MAX_PARAMETER_COUNT = 100
      use Camel Case for enum class names and Upper Case for enum values.
      don't use '_' anywhere except constants and enum values (which are constants).
7.      Don't reuse same variable name in the same class in different contexts: e.g. in method, constructor, class. So you can provide more simplicity for understandability and maintainability.
8.      Don't use same variable for different purposes in a method, conditional etc. Create a new and different named variable instead. This is also important for maintainability and readability.
9.      Don't use non-ASCII chars in variable names. Those may run on your platform but may not on others.
10.      Don't use too long variable names (e.g. 50 chars). Long names will bring ugly and hard-to-read code, also may not run on some compilers because of character limit.
11.      Decide and use one natural language for naming, e.g. using mixed English and German names will be inconsistent and unreadable.
12.      Use meaningful names for methods. The name must specify the exact action of the method and for most cases must start with a verb. (e.g. createPasswordHash)
13.      Obey company naming standards and write method names consistently in application: e.g. getTxtUserName(), getLblUserName(), isStudentApproved(), ... Otherwise readability will reduce and find/replace tools will be unusable.
14.      Obey programming language standards and don't use lowercase/uppercase characters inconsistently: e.g. getUserName, GetUserName, getusername, ...
o      For example for Java,
      use  Mixed Case  for method names: getStudentSchoolType
      use  Mixed Case  for method parameters: setSchoolName(String schoolName)
15.      Use meaningful names for method parameters, so it can documentate itself in case of no documentation.
---

3.      Use meaningful names for variables. Variable name must define the exact explanation of its content.

Yes, use meaningful names.
No, they do not have to be an exact explanation of their usage/content.

If code interrogates four different columns, fourteen, or even forty columns, for example, there is no need to define multiple separate & distinct variables.

Just one; 'c' (or 'rngValue', or 'objCell', or something similar) is adequate.
0
[ fanpages ]IT Services ConsultantCommented:
If you wish to discuss 'best practices', naming standards, & coding conventions I would suggest you begin a new thread.

Many already exist on this topic, in any respect.
0
Kanti PrasadCommented:
Hi

Just to make myself clear I am not saying that using c is wrong . I suggested g as initially c was reading b range of values and then the  changed code was reading g range values. Hence, a more meaning full name would g which will avoid confusion.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.