Consulting

Results 1 to 7 of 7

Thread: VBA code to copy and paste row based on certain criteria

  1. #1
    VBAX Newbie
    Joined
    Oct 2013
    Posts
    2
    Location

    VBA code to copy and paste row based on certain criteria

    Dear all,

    Currently I have an excel table where I have a macro that based on a certain value in a cell (> 1), it will copy, insert new row and paste the entire row x number of times based on the value in the column "COUNT".

    What I need help with is I don't want the row to be copied exactly, but based on the value in "COUNT", for each paste the row will contain only one input value with the following pattern. It's hard to explain, so I hope the table below explains it.

    BEFORE
    count input input input input
    3 A A A
    AFTER
    count input input input input
    3 A A A
    3 A A A
    3 A A A
    3 A A A

    what I would like the macro to do:

    BEFORE
    count input input input input
    3 A A A
    AFTER
    count input input input input
    3 A A A
    1 A
    1 A
    1 A

    So if the value is 10, I want the macro to copy it 10 times but so each row has only one input value with an offset of 1.

    Attached is the excel with the current macro and an example how I wish it works.

    Thank you for your help!

    BR,

    T0NT0
    Attached Files Attached Files

  2. #2
    VBAX Regular
    Joined
    Apr 2013
    Posts
    30
    Location
    hmmm let me see if I understood.
    You want to multiply the number of the rows by the number of what is in cell count??

  3. #3
    VBAX Guru mancubus's Avatar
    Joined
    Dec 2010
    Location
    "A beggar of love, second hand hero...King of Dreams"
    Posts
    2,602
    welcome to the forum.

    not an elegant one but i think it works...

    Dim i As Long, j As Long, rws As Long, LR As Long
     
    LR = Cells.Find("*", , , , xlByRows, xlPrevious).Row
     
    For i = LR To 2 Step -1
        With Cells(i, 1)
            If .Value > 1 Then
                rws = Cells(i, Columns.Count).End(xlToLeft).Column - 1
                Rows((i + 1) & ":" & (i + rws)).Insert
                For j = 1 To rws
                    .Offset(j, j).Value = Cells(i, j + 1).Value
                Next
            End If
        End With
    Next
     
    LR = Cells.Find("*", , , , xlByRows, xlPrevious).Row
    For i = Cells(Rows.Count, 1).End(xlUp).Row To 2 Step -1
        If Application.CountA(Rows(i)) = 0 Then
            Rows(i).Delete
            LR = LR - 1
        End If
    Next
     
    Range("A2:A" & LR).FormulaR1C1 = "=COUNTA(RC[1]:RC[20])"
    Last edited by mancubus; 10-30-2013 at 12:13 PM.
    PLS DO NOT PM; OPEN A THREAD INSTEAD!!!

    1) Posting Code
    [CODE]PasteYourCodeHere[/CODE]
    (or paste your code, select it, click # button)

    2) Uploading File(s)
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) (multiple files can be selected while holding Ctrl key) / Upload Files / Done
    Replace company specific / sensitive / confidential data. Include so many rows and sheets etc in the uploaded workbook to enable the helpers visualize the data and table structure. Helpers do not need the entire workbook.

    3) Testing the Codes
    always back up your files before testing the codes.

    4) Marking the Thread as Solved
    from Thread Tools (on the top right corner, above the first message)

  4. #4
    VBAX Newbie
    Joined
    Oct 2013
    Posts
    2
    Location
    perhaps I should have said the data is quite random.

    There is no pattern to the data. The range of columns with the data is quite larger than in my example.

    For example: The first data entry can start in any column, and then have the next 3 can have data, then again the next few columns will be empty, and then data again and so on.

    attached is a more detailed sample. on the "before and after example" sheet I tried to show how random the data is and how the macro would create new rows.

    Hope this examples it better than my words.
    Attached Files Attached Files

  5. #5

  6. #6
    VBAX Guru mancubus's Avatar
    Joined
    Dec 2010
    Location
    "A beggar of love, second hand hero...King of Dreams"
    Posts
    2,602
    i amended the code in post #3 based on new file.
    PLS DO NOT PM; OPEN A THREAD INSTEAD!!!

    1) Posting Code
    [CODE]PasteYourCodeHere[/CODE]
    (or paste your code, select it, click # button)

    2) Uploading File(s)
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) (multiple files can be selected while holding Ctrl key) / Upload Files / Done
    Replace company specific / sensitive / confidential data. Include so many rows and sheets etc in the uploaded workbook to enable the helpers visualize the data and table structure. Helpers do not need the entire workbook.

    3) Testing the Codes
    always back up your files before testing the codes.

    4) Marking the Thread as Solved
    from Thread Tools (on the top right corner, above the first message)

  7. #7
    VBAX Guru mancubus's Avatar
    Joined
    Dec 2010
    Location
    "A beggar of love, second hand hero...King of Dreams"
    Posts
    2,602
    as rollis indicated, please provide links to your questions in other forums when posting multiple forums.


    for cross-posting etiquette, pls visit:
    http://www.excelguru.ca/content.php?184
    PLS DO NOT PM; OPEN A THREAD INSTEAD!!!

    1) Posting Code
    [CODE]PasteYourCodeHere[/CODE]
    (or paste your code, select it, click # button)

    2) Uploading File(s)
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) (multiple files can be selected while holding Ctrl key) / Upload Files / Done
    Replace company specific / sensitive / confidential data. Include so many rows and sheets etc in the uploaded workbook to enable the helpers visualize the data and table structure. Helpers do not need the entire workbook.

    3) Testing the Codes
    always back up your files before testing the codes.

    4) Marking the Thread as Solved
    from Thread Tools (on the top right corner, above the first message)

Posting Permissions

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