?
Solved

Deleting select rows in Excel

Posted on 2014-01-17
3
Medium Priority
?
245 Views
Last Modified: 2014-01-19
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
0
Comment
Question by:seamus9909
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 52

Accepted Solution

by:
Rgonzo1971 earned 2000 total points
ID: 39790524
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
 

Author Comment

by:seamus9909
ID: 39790693
so you used the sheet I provided, and you run the macro and it creates a new sheet called results?
0
 
LVL 52

Expert Comment

by:Rgonzo1971
ID: 39792084
Hi,

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

Regards
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question