Selective deletion of rows containing certain text.

Colin Hayes · 06-14-2005, 06:15 PM

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

Bob Phillips · 06-14-2005, 06:15 PM

Use filter, menu Data>Filter>Autofilter, and then delete the visible rows.

--
HTH

Bob Phillips

"Colin Hayes" <[email protected]> wrote in message
news:[email protected]...
> 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
>

Colin Hayes · 06-14-2005, 08:05 PM

In article <[email protected]>, Bob Phillips
<[email protected]> writes
>Use filter, menu Data>Filter>Autofilter, 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.

Bob Phillips · 06-14-2005, 08:05 PM

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" <[email protected]> wrote in message
news:[email protected]...
> In article <[email protected]>, Bob Phillips
> <[email protected]> writes
> >Use filter, menu Data>Filter>Autofilter, 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.

Bob Phillips · 06-14-2005, 08:05 PM

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" <[email protected]> wrote in message
news:[email protected]...
> In article <[email protected]>, Bob Phillips
> <[email protected]> writes
> >Use filter, menu Data>Filter>Autofilter, 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 Hayes · 06-14-2005, 11:05 PM

In article <##[email protected]>, Bob Phillips
<[email protected]> 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

Bob Phillips · 06-15-2005, 05:05 AM

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" <[email protected]> wrote in message
news:[email protected]...
> In article <##[email protected]>, Bob Phillips
> <[email protected]> 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 Hayes · 06-15-2005, 11:05 AM

In article <[email protected]>, Bob Phillips
<[email protected]> 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

Bob Phillips · 06-15-2005, 11:05 AM

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" <[email protected]> wrote in message
news:[email protected]...
> In article <[email protected]>, Bob Phillips
> <[email protected]> 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

Colin Hayes · 06-15-2005, 06:05 PM

In article <[email protected]>, Bob Phillips
<[email protected]> 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" <[email protected]> wrote in message
>news:[email protected]...
>> In article <[email protected]>, Bob Phillips
>> <[email protected]> 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
>
>