Delete All Rows except Row Containing Text and Row Above

[JinkyJulie]

Hi all,

Cannot figure this out... I know its simple enough...

I wish to scan through records (about 22500) looking for specific text "ABC123", I wish to delete ALL rows except the row containing the text and the row immediately above... leaving only what's bolded

1	Apple
2	Pear
3	Banana
4	ABC123
5	Cherry
6	Pomegranate
7	ABC123
8	Orange
9	Strawberry
10	ABC123

Thanks you,

JJ

[Paul_Hossler]

I'd use something like this, adjusting ranges as necessary. I put the data in Col A

Option Explicit
Sub DeleteSome()

    Dim iRow As Long
    
    Application.ScreenUpdating = False
    For iRow = 1 To ActiveSheet.Cells(1, 1).CurrentRegion.Rows.Count - 1
        If Not (ActiveSheet.Cells(iRow, 1).Value = "ABC123" Or ActiveSheet.Cells(iRow + 1, 1).Value = "ABC123") Then
            ActiveSheet.Cells(iRow, 1).Value = True
        End If
    Next iRow
    
    On Error Resume Next
    ActiveSheet.Cells(1, 1).CurrentRegion.SpecialCells(xlCellTypeConstants, xlLogical).EntireRow.Delete
    On Error GoTo 0
    
    Application.ScreenUpdating = False
End Sub

[Kevin#]

and a slightly different approach using nested IF statements

Sub DeleteVariousRows()
    Application.ScreenUpdating = False
    Dim lastRow As Long, firstRow As Long, i As Long
'determine last row of data (using columnA)
    lastRow = ActiveSheet.Cells(1048576, "A").End(xlUp).Row
'enter the row number of the first row of data
    firstRow = 2
'check value of cell in current row and row below
    For i = lastRow To firstRow Step -1
        If ActiveSheet.Cells(i, "A").Value = "ABC123" Then
        'do nothing
        Else
            If ActiveSheet.Cells(i + 1, "A").Value = "ABC123" Then
            'do nothing
            Else
            ActiveSheet.Rows(i).EntireRow.Delete
            End If
        End If
    Next i
    Application.ScreenUpdating = True
End Sub

[JinkyJulie]

Thank you both for you time... after some tweaking and time to understand them, I got them both to work...

That saved me a load of time....

Best wishes to you in 2016!!!!