Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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. |
#4
|
|||
|
|||
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. |
#5
|
|||
|
|||
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. |
#6
|
|||
|
|||
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 |
#7
|
|||
|
|||
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 |
#8
|
|||
|
|||
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 |
#9
|
|||
|
|||
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 |
#10
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
opening text files that have more rows than 65536 | Excel Worksheet Functions | |||
Autofitting a row | Excel Discussion (Misc queries) | |||
delete empty rows between rows with text | Excel Discussion (Misc queries) | |||
copy / paste selective rows | Excel Discussion (Misc queries) | |||
how do I easily convert a single column of text (multiple rows si. | Excel Discussion (Misc queries) |