Deleting previous and next row

[weep]

Hey,

It would be awesome if someone could help me with following:
I need to find a row with some specifik text and then delete it and previous and next row. I have found a script that deletes this one specific row, but how do I remove the rest?

Sub Loop_Example()
    Dim Firstrow As Long
    Dim Lastrow As Long
    Dim Lrow As Long
    Dim CalcMode As Long
    Dim ViewMode As Long

    With Application
        CalcMode = .Calculation
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
    End With

    'We use the ActiveSheet but you can replace this with
    'Sheets("MySheet")if you want
    With ActiveSheet

        'We select the sheet so we can change the window view
        .Select

        'If you are in Page Break Preview Or Page Layout view go
        'back to normal view, we do this for speed
        ViewMode = ActiveWindow.View
        ActiveWindow.View = xlNormalView

        'Turn off Page Breaks, we do this for speed
        .DisplayPageBreaks = False

        'Set the first and last row to loop through
        Firstrow = .UsedRange.Cells(1).Row
        Lastrow = .UsedRange.Rows(.UsedRange.Rows.Count).Row

        'We loop from Lastrow to Firstrow (bottom to top)
        For Lrow = Lastrow To Firstrow Step -1

            'We check the values in the A column in this example
            With .Cells(Lrow, "R")

                If Not IsError(.Value) Then

                    If .Value = "V90 Verksamhetsankn. utr-" Then .EntireRow.Delete
                    'This will delete each row with the Value "ron"
                    'in Column A, case sensitive.

                End If

            End With

        Next Lrow

    End With

    ActiveWindow.View = ViewMode
    With Application
        .ScreenUpdating = True
        .Calculation = CalcMode
    End With

End Sub

thanks in advance guys!

 

[Yahya]

may be this will help

Sub Test()
Dim LR As Long, i As Long
LR = Range("R" & Rows.Count).End(xlUp).Row
For i = LR To 2 Step -1
If Range("R" & i).Value = "V90 Verksamhetsankn. utr-" Then
    Range("R" & i).Offset(-1).Resize(3).EntireRow.Delete
End If
Next i
End Sub

 

[AlphaFrog]

Hello and welcome.

Change this line...

If .Value = "V90 Verksamhetsankn. utr-" Then .EntireRow.Delete

To this...

If .Value = "V90 Verksamhetsankn. utr-" Then [COLOR="Red"].Offset(-1).Resize(3)[/COLOR].EntireRow.Delete

 

[weep]

Worked like a charm, thank you guys!

 

[UHCAron]

I'm using the same macro, but I need to keep the first row, I don't want this one instance deleted even though it has the criteria. How do I offset the first row?

Firstrow = .UsedRange.Cells(1).Row ???


Duh found it .UsedRange.Cells(1).Row + 1