Solved insert row for active row

August 12, 2015 at 20:27:28
Specs: Windows 7
Hi,

i tried one of the formula available here but im getting error "run-time error '13' type mismatch".

i'm trying to insert automatically row based on column C. the excel data is:
Col A Col B Col C
xxx 10 2
yyy 5 3

the result it should be;
Col A Col B Col C
xxx 10 2
xxx 10 2
yyy 5 3
yyy 5 3
yyy 5 3

the macro that i insert:
Private Sub CommandButton1_Click()
'Determine last Row with data in Column c
lastRw = Cells(Rows.Count, "c").End(xlUp).Row
'Loop through rows in reverse order
For rw = lastRw To 2 Step -1
'If Column c > 0, insert Rows and copy data
If Cells(rw, "c") > 0 Then
For newRw = 1 To Cells(rw, "c")
'Insert/Copy rows
Cells(rw, "c").EntireRow.Copy
Cells(rw, "c").EntireRow.Insert shift:=xlDown
Next
End If
Next

End Sub


See More: insert row for active row

Report •

#1
August 13, 2015 at 00:55:57
i hv solve the prob on error 13.

but my row is copied with extra 1 line.
i do not know how to correct it.


Report •

#2
August 13, 2015 at 06:19:02
✔ Best Answer
First, a posting tip:

Before posting any more example data and/or code in this forum, please click on the blue line at the end of this post and read the instructions found via that link. Thanks!

As far as your question...

This instruction tells VBA to insert rows based on the value in Column C. Therefore, if the value is 3, it will insert 3 rows.

For newRw = 1 To Cells(rw, "c")

Since you already have an existing row, you actually want to insert 1 row less than the value in Column C. Try this instead:

For newRw = 1 To Cells(rw, "C") - 1

You will note that I also used an upper case C. That won't change the operation of the code in any manner, it's just considered to be "cleaner" since cell references are typically written in upper case. In other words, it's not =sum(a1:b3) it's =SUM(A1:B3).

If you are going to be working with VBA, you might want to review the debugging techniques offered in this How To:

http://www.computing.net/howtos/sho...

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#3
August 13, 2015 at 17:57:54
Thank you.
it is solved.

Report •
Related Solutions


Ask Question