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