Selective deletion of rows containing certain text.

C

Colin Hayes

HI

I have a worksheet which contains some rows which I need to delete
selectively.

Basically , in my worksheet I need Excel to identify the rows containing
somewhere in them the text 'ABCD' , and then to delete them.

As long as the text is somewhere in the row it should delete that row
and any others containing it. Is this possible? If I could run a small
macro then that would be great.


Best Wishes
 
C

Colin Hayes

Bob Phillips said:
Use filter, menu Data>Filter>Autofilter, and then delete the visible rows.
Click to expand...

Hi Bob

OK Thanks for that - I made a short macro based on your advice and it
solves it very neatly.

BTW , do you know a way to automatically select every other row
(1,3,5,7,9,11.....to end of file) of a worksheet and delete these?

Hope you can help with that too. It's for a very specific and important
purpose.

Last one I promise.

Thanks.
 
B

Bob Phillips

Dim iLastRow As Long
Dim i As Long
iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
If iLastRow Mod 2 = 1 Then
iLastRow = iLastRow - 1
End If

For i = iLastRow To 1 Step -2
Cells(i, "A").EntireRow.Delete
Next i
 
B

Bob Phillips

Colin,

My previous post was probably the 'wrong' other row. Try this if so

Dim iLastRow As Long
Dim i As Long
iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
If iLastRow Mod 2 = 0 Then
iLastRow = iLastRow - 1
End If

For i = iLastRow To 2 Step -2
Cells(i, "A").EntireRow.Delete
Next i
 
C

Colin Hayes

Bob Phillips said:
Colin,

My previous post was probably the 'wrong' other row. Try this if so

Dim iLastRow As Long
Dim i As Long
iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
If iLastRow Mod 2 = 0 Then
iLastRow = iLastRow - 1
End If

For i = iLastRow To 2 Step -2
Cells(i, "A").EntireRow.Delete
Next i
Click to expand...
Hi Bob

Yes , that got it - thanks for your help on this - I'm really grateful.

I've worked that code and your previous tip into a macro which cleans up
my worksheets really well. Now all I need to do is get the custom
autofilter to accept a second 'or' parameter. I don't see why it
shouldn't accept this programmatically , but it objects every time. Oh
well , life's not perfect.

Anyway Bob , thanks again for your expertise.

Best wishes

Colin
 
B

Bob Phillips

Colin,

here is an example that works for me

Columns("A:A").AutoFilter Field:=1, _
Criteria1:="=Bob", _
Operator:=xlOr, _
Criteria2:="=Lynne"
 
C

Colin Hayes

Bob Phillips said:
Colin,

here is an example that works for me

Columns("A:A").AutoFilter Field:=1, _
Criteria1:="=Bob", _
Operator:=xlOr, _
Criteria2:="=Lynne"
Click to expand...

HI Bob

Yes , that works I find , but it's the next one that causes the problem.
Here's my code for selecting and deleting lines :

Cells.Select
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:="=*is deleted*",
Operator:=xlOr, _
Criteria2:="=*changed on*"
Selection.Delete Shift:=xlUp

If I try to add another criteria (select and delete lines with the word
'added' in them) , I get code errors.

I don't know why adding

, Operator:=xlOr, _
Criteria3:="=*added*"

shouldn't work. There are ways around it I think , but all a little
cumbersome.

I do note that the user interface via the menu options does only allow
two entries , but I thought it could be implemented via VBA.




Best Wishes


Colin
 
Top