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