Add a row and delete a row by searching a specific text with VBA

Hi, besis.

If you find the target row and Target.Insert Shift:=xlDown inserts a blank row before the target row.
So deleting one row before the target row ends up with deleting the row you've just inserted.
Do I make sense?

Yes, that is true. My mistake. I edited my post. At the macro for deleting has search criteria -1.

Do you have multiple rows that contain value 1 or -1 in column A?

Hi,

Yes I have multiple rows that contain value 1 or -1 in column A. I was thinking maybe is good idea for the macro for deleting to put the value -1 on column B.

Thanks again:
B

I'm not sure this is what you want, but try it anyway.

Sub Test()
    Dim foundCell As Range
    Dim firstCell As Range
    Dim value1, value2
    
    value1 = 1
    value2 = -1
    
    With Range("A:A")
        Set foundCell = .Find(What:=value1, LookAt:=xlWhole)
        If Not foundCell Is Nothing Then
            foundCell.EntireRow.Insert Shift:=xlDown
            Set firstCell = foundCell
            
            Do
                Set foundCell = .FindNext(foundCell)
                If foundCell.Address = firstCell.Address Then
                    Exit Do
                Else
                    foundCell.EntireRow.Insert Shift:=xlDown
                End If
            Loop
        End If
    
        Set foundCell = .Find(What:=value2, LookAt:=xlWhole)
        If Not foundCell Is Nothing Then
            If WorksheetFunction.CountA(foundCell.Offset(-1).EntireRow) > 0 Then
                MsgBox "Can't delete row, row contains value"
            Else
                foundCell.Offset(-1).EntireRow.Delete
            End If
            
            Set firstCell = foundCell
            
            Do
                Set foundCell = .FindNext(foundCell)
                If foundCell.Address = firstCell.Address Then
                    Exit Do
                Else
                    If WorksheetFunction.CountA(foundCell.Offset(-1).EntireRow) > 0 Then
                        MsgBox "Can't delete row, row contains value"
                    Else
                        foundCell.Offset(-1).EntireRow.Delete
                    End If
                End If
            Loop
        End If
    End With
End Sub

Hi Yujin,

Your code worked perfectly after I separate your code. For the value 1 for the column A, and the value -1 for column B.
Searching for 1 and -1 in A:A column didn´t work.

Thank you very much for your help.
All the best.
B.

I'm happy to have been able to help you out