PDA

View Full Version : VBA code to copy and paste row based on certain criteria



T0NT0
10-30-2013, 06:50 AM
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
10-30-2013, 08:45 AM
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
10-30-2013, 09:14 AM
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
10-30-2013, 11:47 AM
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
10-30-2013, 11:49 AM
Cross-post:
http://www.excelfox.com/forum/f2/vba-code-to-copy-and-paste-row-based-on-certain-criteria-1556/
http://www.ozgrid.com/forum/showthread.php?t=183757&highlight=VBA+code+copy+paste+row+based+criteria
http://www.mrexcel.com/forum/excel-questions/735765-visual-basic-applications-code-copy-paste-row-based-certain-criteria-need-help-paste-part.html

mancubus
10-30-2013, 12:15 PM
i amended the code in post #3 based on new file.

mancubus
10-30-2013, 12:18 PM
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