Solved

VBA to delete rows that do not contain the words Proven or Under Investigation

Posted on 2013-01-24
5
223 Views
Last Modified: 2013-01-25
Can someone assist in providing me with some code to delete rows that do not contain the words Proven or Under Investigation [which could be in either Lower or Uppercase] The Column these will be found in is 'I' so I only want to keep the rows that contain either of those words.

Thank you
0
Comment
Question by:Jagwarman
  • 3
  • 2
5 Comments
 
LVL 18

Expert Comment

by:krishnakrkc
ID: 38813555
Hi

Try

Option Explicit

Sub kTest()
    
    Dim r   As Range
    
    Set r = Range("I2:I1000")        'adjust to suit
    
    With r
        .AutoFilter 1, "<>Proven", xlAnd, "<>Under investigation"
        On Error Resume Next
        .Offset(1).Resize(.Rows.Count - 1, 1).SpecialCells(12).EntireRow.Delete
        .AutoFilter
    End With
    
    Application.ScreenUpdating = 1
    
End Sub

Open in new window


Kris
0
 

Author Comment

by:Jagwarman
ID: 38813597
Unfortunately that deletes the ones I want to keep :-(
0
 
LVL 18

Expert Comment

by:krishnakrkc
ID: 38813610
Hi

Are you sure ? Your title says you want to keep Proven and Under inverstigation rows, right ? If not,

replace
.AutoFilter 1, "<>Proven", xlAnd, "<>Under investigation"

Open in new window


with

.AutoFilter 1, "=Proven", xlOr, "=Under investigation"

Open in new window


Kris
0
 

Author Comment

by:Jagwarman
ID: 38813703
yes I want to keep Proven and under investigation but when I run it, it deletes those and keeps everything else. I am using Excel 2010
0
 
LVL 18

Accepted Solution

by:
krishnakrkc earned 500 total points
ID: 38813835
Hi

PFA.

Kris
DeleteRows.xlsb
0

Featured Post

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

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

Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
My experience with Windows 10 over a one year period and suggestions for smooth operation
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
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…

830 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