Deleting select rows in Excel

I have a spreadsheet , that I have attached.  There are thousands of rows that we want to delete automatically if a certain condition is true.

The rows represent unique records that are kind of nested. We want to delete the rows associAted with the  "ABC STAR" as an example:

10017410203395 REBECCA BUFFINGTON FOR DCB 10/31/2013 US
PNC Star
IJR ISHARES CORE S&P SMALL-CAP E 32.000% 30.680% 1.320%
CASH 3.000% 4.010% 1.010%

if we find PNC STAR, we want to delete the row above "10017410203395 ...." and delete the rows following PNC STAR until we hit the next occurance of 10017...." .  We want to repeat this until all rows are deleted that we want. in the example spreadsheet you will see :  We do not want those rows to be deleted.  Is there anyway to write a macro or something to do this?  This is example data and does not contain and private or confidentail information.

I have tried using the statement in Column C , but its marking everything as delete. However if you look at row 159 -161 those should be marked as Keep, as they dont contain PNC Star.
Test-Drift.xlsx
seamus9909Asked:
Who is Participating?
 
Rgonzo1971Connect With a Mentor Commented:
Hi,

Is this what you want ?

I used this code

Sub Macro()
Application.ScreenUpdating = False
LastNameRw = Range("A" & Cells.Rows.Count).End(xlUp).Row
RwIdx = Range("A" & Cells.Rows.Count).End(xlUp).Row

Do While RwIdx > 1
    
    Debug.Print Range("A" & RwIdx)
    If Range("A" & RwIdx) = "PNC Star" Then
        Range("A" & RwIdx - 1 & ":A" & LastNameRw).EntireRow.Delete
    End If
    If Range("A" & RwIdx) Like "[0-9][0-9][0-9][0-9][0-9][0-9]*" Then
        LastNameRw = RwIdx - 1
    End If
    RwIdx = RwIdx - 1
Loop
Application.ScreenUpdating = False
End Sub

Open in new window

Regards
Test-DriftV1.xlsm
0
 
seamus9909Author Commented:
so you used the sheet I provided, and you run the macro and it creates a new sheet called results?
0
 
Rgonzo1971Commented:
Hi,

No. I copied the sheet and run the macro on the copied sheet

Regards
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.