VBA code to copy and paste row based on certain criteria

[T0NT0]

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

[Aranell]

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??

[mancubus]

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])"

[T0NT0]

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.

[rollis13]

Cross-post:
http://www.excelfox.com/forum/f2/vba...criteria-1556/
http://www.ozgrid.com/forum/showthre...based+criteria
http://www.mrexcel.com/forum/excel-q...aste-part.html

[mancubus]

i amended the code in post #3 based on new file.

[mancubus]

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