Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    San Clemente, California, USA
    Posts
    130
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Creating Duplicate Rows based on a cell (2002)

    I've got a tough one!

    I have to change my spreadsheet for uploading to my website. For each category that a product belongs to i need to have a duplicate row of the same data with the different category name on each duplicated line.

    I'm hoping someone can think of a way I can do this in VBA, because I have 30,000 items that I need to seperate out!

    It's really hard to explain in words, so I am going to upload an example spreadsheet and hopefully someone will take the time to look at it... Sheet 1 is what I have, Sheet 2 is what I need it to be....

  2. #2
    3 Star Lounger
    Join Date
    Feb 2003
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Creating Duplicate Rows based on a cell (2002)

    The following may help get you started.
    It makes a separate row for each category, but in the reverse order that you show.
    It's a bit late here and I don't have time to fix this part.
    Hope this helps

    <pre>Option Explicit

    Sub SeparateCategories()

    Dim Lrow As Long
    Dim str1 As String
    Dim str2 As String

    Dim i As Integer
    Dim j As Integer

    Lrow = Range("G100").End(xlUp).Row
    For i = Lrow To 2 Step -1
    While InStr(1, Cells(i, 7) .Text, ":", 1) > 1
    j = InStr(1, Cells(i, 7) .Text, ":", 1)
    'get the 1st string
    str1 = Mid(Cells(i, 7) .Text, 1, j - 1)
    'get the rest of the string
    str2 = Mid(Cells(i, 7) .Text, j + 1)
    'insert and copy the row
    Cells(i + 1, 7).EntireRow.Insert
    Cells(i + 1, 7).EntireRow.FillDown
    'change categories
    Cells(i, 7) = str2
    Cells(i + 1, 7) = str1

    Wend
    Next i


    End Sub

    </pre>


  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Creating Duplicate Rows based on a cell (2002)

    Here is a variation on Paul's code that will preserve the order of the categories (and work with more than 100 rows):

    Sub SeparateCategories()
    Dim lRow As Long
    Dim i As Long
    Dim j As Long
    Dim arrParts() As String
    Dim strPart1 As String

    Application.ScreenUpdating = False

    lRow = Range("G65536").End(xlUp).Row
    For i = lRow To 2 Step -1
    If Not Range("G" & i) = "" Then
    arrParts = Split(Range("G" & i), ":")
    Range("G" & i) = arrParts(0)
    For j = UBound(arrParts) To 1 Step -1
    Range("A" & i).EntireRow.Copy
    Range("A" & (i + 1)).EntireRow.Insert
    Range("G" & (i + 1)) = arrParts(j)
    Next j
    End If
    Next i

    Application.CutCopyMode = False
    Application.ScreenUpdating = True
    End Sub

  4. #4
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    San Clemente, California, USA
    Posts
    130
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Creating Duplicate Rows based on a cell (2002)

    You guys Rock!!!!

    Thank you so much... I would have had to do this manually (ouch) if it wasn't for your help...

    Hans~ You have personally helped me with a lot of VBA in the last few years, I really cannot tell you how much I appreciate your help & skill. <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •