Selective deletion of rows containing certain text.
 

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

Use filter, menu DataFilterAutofilter, and then delete the visible rows.

--
HTH

Bob Phillips

"Colin Hayes" wrote in message
...
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

In article , Bob Phillips
writes
Use filter, menu DataFilterAutofilter, and then delete the visible rows.


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.

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


--
HTH

Bob Phillips

"Colin Hayes" wrote in message
...
In article , Bob Phillips
writes
Use filter, menu DataFilterAutofilter, and then delete the visible
rows.


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.

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


--
HTH

Bob Phillips

"Colin Hayes" wrote in message
...
In article , Bob Phillips
writes
Use filter, menu DataFilterAutofilter, and then delete the visible
rows.


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.

In article , Bob Phillips
writes
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


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

Colin,

here is an example that works for me

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


--
HTH

Bob Phillips

"Colin Hayes" wrote in message
...
In article , Bob Phillips
writes
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


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

In article , Bob Phillips
writes
Colin,

here is an example that works for me

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



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

Hi Colin,

When using more than 2 criteria on the same data, best to use the Advanced
Filter. See http://www.contextures.com/xladvfilter01.html for details, and
post back with a new thread if you get stuck.

Regards

Bob

"Colin Hayes" wrote in message
...
In article , Bob Phillips
writes
Colin,

here is an example that works for me

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



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

In article , Bob Phillips
writes
Hi Colin,

When using more than 2 criteria on the same data, best to use the Advanced
Filter. See http://www.contextures.com/xladvfilter01.html for details, and
post back with a new thread if you get stuck.

Regards

Bob

HI Bob

OK I'll go and have a look there.

Thanks again.

Colin

"Colin Hayes" wrote in message
...
In article , Bob Phillips
writes
Colin,

here is an example that works for me

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



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