Delete a Row Based on 2 Rows

[nniedzielski]

I have a list of data that I am building a macro to cleanup. In my first column I have values that range from 2 to 8.
This has already been filtered from highest to lowest so the2’s are at the bottom and so on…
I need to delete every row except the 8’s and the first rowof 7. There will always be more than one row of 7, but I only need to keep one.
My first thought was to use this code:

[FONT=Calibri][SIZE=3][COLOR=#000000]finalrow = Cells(Rows.Count, 1).End(xlUp).Row[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]For i = finalrowTo 2 Step -1[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]If Cells(i, 2)<> 8 And Cells.Offset(Cells(i, 2), -1, 0).Value = 8 Then[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Cells(i,1).EntireRow.Delete[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Else[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]'donothing[/COLOR][/SIZE][/FONT]

[FONT=Calibri][SIZE=3][COLOR=#000000]End If[FONT=Times New Roman][SIZE=3][COLOR=#000000][FONT=Calibri]
[SIZE=3][COLOR=#000000]Next i

[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT]

But it doesn’t seem to do what I want it to do. How can I savethe last row where the value is 7 and all the 8’s?

thanks,

 

[Fluff]

How about

If Cells(i - 1, 2).Value <> 8 Then

 

[nniedzielski]

perfect solution, thank you

 

[KennyGreens]

Hi, you could try:

Dim firstrow As Long
Dim lastrow As Long

firstrow = Cells.Find("7", searchorder:=xlByRows, searchdirection:=xlNext).Row
lastrow = Cells.Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row

Range("B" & firstrow + 1 & ":A" & lastrow).EntireRow.Delete

Edit: Fluff beat me to it again

 

[Fluff]

perfect solution, thank you

You're welcome & thanks for the feedback