Change in code to get it to insert two blank rows instead of one and paste text

Hello

I have this code for searching a column when a number changes and insert rows above and below when there is a change.

Please Login or Register  to view this content.

So fx. Column C:
1
1
1
2
2

Have to be:

new row = "Text"
1
1
1
new row "Text 2"
new row "text"
2
2
new row "text 2"
new row "Text"

So when a new value begins: "Text"
When it ends, "text 2"
When the new value begins, same "text"
when it ends, same "text 2

Appreciate any help.
Kind regards

I have assumed that column C has a header in cell C1, and that there are no blanks.

Please Login or Register  to view this content.

Thanks for the reply!

There shouldn't be any blank cells, and yes there is a header in Row 1.
However, I have the extracted the information I need to further dismantle from a bigger sheet with the following forumlas:
=IFERROR(SMALL(IF(ISNUMBER(MATCH(Sheet1!$A$2:$A$499;Sheet1!$B$2:$B$499;0));ROW(Sheet1!$A$2:$A$499)-ROW(Sheet1!$A$2)+1);ROWS($A$2:A2));"") In Column A, and then dragged it down.
In the rest of the columns from B to I I have dragged down the following: =IF($A2="";"";INDEX(Sheet1!$A$2:$E$499;$A2;MATCH(B$1;Sheet1!$A$1:$E$1;0)))

So when I insert a new row it apparently just replaces the one with the formula in, is there anyway to fix this - ?

I've changed the ranges from your code to my workbook:

Please Login or Register  to view this content.

How about: the code copies values from the current sheet to a new blank sheet and then processes that, or just converts the formulas to values - or just use the values from the bigger sheet? Are the numbers going to change after you run this code, so that you still need formulas?

You could also look at the Filtering and Subtotaling functionality (Data / Outline) and use that on the bigger sheet - no formulas, no extraction.....

Try this version

Please Login or Register  to view this content.

Excelsemi,

2 pointers -

1. I have added code tags to your post. As per forum rule 3, you need to use them whenever you put any code in your post. Please add them in future. In order to put code tags, either type [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] at the end of it, OR you can highlight your code and click the # icon at the top of your post window.

2. This thread has been posted in the Commercial services subforum, hence will be closed.