Link to home
Start Free TrialLog in
Avatar of Sonia Bowditch
Sonia BowditchFlag for Guernsey

asked on

Delete Rows not containing string

I have a download of security groups from Active Directory.  I want to delete rows which do not contain the string:

domain.local/group/group/

Example:
Column A          Column B
Group                 Container
group A                 domain.local/group/group/group A
group B                 domain.local/group/group/group B
group C                 domain.local/group/group/group C
group D                 domain.local/group/group/group D
group E                 domain.local/group/group/group E
group F                  domain.local/group/group/group F
group G                 domain.local/group/group/group G
group H                 domain.local/group/group/group H
group I                 domain.local/group/group/group I
Group J                 domain.local/builtin/Group J
Group K                 domain.local/builtin/Group K
Group L                 domain.local/builtin/Group L
Group M                 domain.local/builtin/Group M
Group N                 domain.local/builtin/Group N

I have tried the following VBA but I can't seem to get it to work properly

Sub sbDelete_Rows_IF_Cell_Cntains_String_Text_Value()
Dim lRow As Long
Dim iCntr As Long
lRow = 20
For iCntr = lRow To 1 Step -1
    If Cells(iCntr, 1) <> "domain.local/Builtin/" Then
        Rows(iCntr).Delete
    End If
Next
End Sub
group G      domain.local/group/group/group G
group H      domain.local/group/group/group H
group I      domain.local/group/group/group I
Group J      domain.local/builtin/Group J
Group K      domain.local/builtin/Group K
Group L      domain.local/builtin/Group L
Group M      domain.local/builtin/Group M
Group N      domain.local/builtin/Group N
Avatar of Rob Henson
Rob Henson
Flag of United Kingdom of Great Britain and Northern Ireland image

You can use AutoFilter rather than VBA.

Apply a filter and on that column use the dropdown to show Filter Options.

You can apply a "contains"  filter simply by typing in the input box. However a  "does not contain" filter will be in the text filter options.

When you have applied the filter, if you now select the visible data and press Ctrl & - you will be able to delete the rows; only those that are visible will be deleted.

Deactivate the filter and you will see the remaining rows.
SOLUTION
Avatar of Rob Henson
Rob Henson
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Sonia Bowditch

ASKER

Thank you both for your help.
You're welcome Sonia!