Sonia Bowditch
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/g roup A
group B domain.local/group/group/g roup B
group C domain.local/group/group/g roup C
group D domain.local/group/group/g roup D
group E domain.local/group/group/g roup E
group F domain.local/group/group/g roup F
group G domain.local/group/group/g roup G
group H domain.local/group/group/g roup H
group I domain.local/group/group/g roup 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_Cnta ins_String _Text_Valu e()
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/g roup G
group H domain.local/group/group/g roup H
group I domain.local/group/group/g roup 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
domain.local/group/group/
Example:
Column A Column B
Group Container
group A domain.local/group/group/g
group B domain.local/group/group/g
group C domain.local/group/group/g
group D domain.local/group/group/g
group E domain.local/group/group/g
group F domain.local/group/group/g
group G domain.local/group/group/g
group H domain.local/group/group/g
group I domain.local/group/group/g
Group J domain.local/builtin/Group
Group K domain.local/builtin/Group
Group L domain.local/builtin/Group
Group M domain.local/builtin/Group
Group N domain.local/builtin/Group
I have tried the following VBA but I can't seem to get it to work properly
Sub sbDelete_Rows_IF_Cell_Cnta
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/g
group H domain.local/group/group/g
group I domain.local/group/group/g
Group J domain.local/builtin/Group
Group K domain.local/builtin/Group
Group L domain.local/builtin/Group
Group M domain.local/builtin/Group
Group N domain.local/builtin/Group
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you both for your help.
You're welcome Sonia!
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.