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

[Excelsemi]

Hello

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

Sub InsertRows()
  Dim lastRow As Long
  Dim rowPtr As Long
  
  lastRow = Range("C" & Rows.Count).End(xlUp).Row
  For rowPtr = lastRow To 2 Step -2
    If Not IsEmpty(Range("C" & rowPtr)) Then
      If Range("C" & rowPtr) <> Range("C" & rowPtr - 1) Then
        Range("C" & rowPtr).EntireRow.Insert
      End If
    End If
  Next
End Sub

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

[Bernie Deitrick]

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

Sub InsertRowsV2()
    Dim lastRow As Long
    Dim rowPtr As Long
    Dim strT1 As String
    Dim strT2 As String
    
    strT1 = "Text 1"
    strT2 = "Text 2"
    
    lastRow = Range("C" & Rows.Count).End(xlUp).Row
    Range("C" & lastRow + 1).Value = strT2
    For rowPtr = lastRow To 3 Step -1
        If Range("C" & rowPtr) <> Range("C" & rowPtr - 1) Then
            Range("C" & rowPtr).Resize(2).EntireRow.Insert
            Range("C" & rowPtr).Value = strT2
            Range("C" & rowPtr + 1).Value = strT1
        End If
    Next rowPtr
    
    Range("C2").EntireRow.Insert
    Range("C2").Value = strT1

End Sub

[Excelsemi]

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:

Sub InsertRowsV2()
    Dim lastRow As Long
    Dim rowPtr As Long
    Dim strT1 As String
    Dim strT2 As String
    
    strT1 = "Begin-Total"
    strT2 = "End-Total"
    
    lastRow = Range("B" & Rows.Count).End(xlUp).Row
    Range("B" & lastRow + 1).Value = strT2
    For rowPtr = lastRow To 3 Step -1
        If Range("B" & rowPtr) <> Range("B" & rowPtr - 1) Then
            Range("B" & rowPtr).Resize(2).EntireRow.Insert
            Range("B" & rowPtr).Value = strT2
            Range("B" & rowPtr + 1).Value = strT1
        End If
    Next rowPtr
    
    Range("B2").EntireRow.Insert
    Range("B2").Value = strT1

End Sub

[Bernie Deitrick]

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

[Excelsemi]

hmm, yea okay.. I could perhaps consider that.

No the numbers are not gonna change, they are the same all the way through this, so I wouldn't need the formulas no..only the information set up in the right way because I have to put it in a specific system.
It was a way to match cells in two different columns and copy the information from only the first column with the same value, and then take all the info from the rest of the columns without copying the matching value in column b and putting that in a new sheet.
But the whole excercise is a bit complex to me - that's why I've tried to do it this way, but for sure there is a smarter way.
The basic thing is that I have several columns and rows, the first two columns consists of some of the same numbers, whenever there is a number in Column A that matches a number in B, I need to copy the information from that column B and the specifc row into a new row with the text "Begin-total" and also a row below with text "end-total"

[Bernie Deitrick]

Try this version

Sub InsertRowsV3()
    Dim lastRow As Long
    Dim rowPtr As Long
    Dim strT1 As String
    Dim strT2 As String
    Dim shtS As Worksheet
    Dim shtT As Worksheet
    
    Set shtS = ActiveSheet
    Set shtT = Worksheets.Add
    shtT.Name = "Output " & Format(Now(), "hh-mm")
        
    shtS.UsedRange.Copy
    shtT.Range("A1").PasteSpecial xlPasteValuesAndNumberFormats
    
    strT1 = "Begin-Total"
    strT2 = "End-Total"
    
    With shtT
        lastRow = .Range("B" & .Rows.Count).End(xlUp).Row
        .Range("B" & lastRow + 1).Value = strT2
        For rowPtr = lastRow To 3 Step -1
            If .Range("B" & rowPtr) <> Range("B" & rowPtr - 1) Then
                .Range("B" & rowPtr).Resize(2).EntireRow.Insert
                .Range("B" & rowPtr).Value = strT2
                .Range("B" & rowPtr + 1).Value = strT1
            End If
        Next rowPtr
        
        .Range("B2").EntireRow.Insert
        .Range("B2").Value = strT1
    End With
End Sub

[arlu1201]

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] before your code and [/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.