Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
entirerow.delete multiple text conditions
Hi,
Total newbie question here. I'd like some help with deleting entire rows based on multiple text conditions. In hunting around on the group, I snagged this macro, which will work for one text condition. How can I change it to make it select all cells on the sheet named Sheet2, look for multiple conditions and delete rows based on all of them in a single macro? Sub deleteWithI() For Each myCell In Selection If myCell = "I" Then myCell.EntireRow.Delete Next myCell End Sub TIA, Jocelyn |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
entirerow.delete multiple text conditions
Sub deleteWithMultiple()
Dim rng as Range, mycell as Range For Each myCell In Selection if mycell = "I" or mycell = "J" or mycell = "B" or _ mycell = "T" then if rng is nothing then set rng = mycell else set rng = Union(rng,mycell) end if End if Next myCell if not mycell is nothing then _ mycell.EntireRow.Delete End Sub -- Regards, Tom Ogilvy "Jocelyn" wrote in message om... Hi, Total newbie question here. I'd like some help with deleting entire rows based on multiple text conditions. In hunting around on the group, I snagged this macro, which will work for one text condition. How can I change it to make it select all cells on the sheet named Sheet2, look for multiple conditions and delete rows based on all of them in a single macro? Sub deleteWithI() For Each myCell In Selection If myCell = "I" Then myCell.EntireRow.Delete Next myCell End Sub TIA, Jocelyn |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
entirerow.delete multiple text conditions
As written, it compiles fine for me - so you must have screwed it up in some
way when you made your changes. Perhaps if you post what you did, it can be corrected. -- Regards, Tom Ogilvy Jocelyn wrote in message om... Hi Tom - For some reason, this didn't work for me. I pasted the code into a module and replaced your I, J, B, & T with some of the elements I wanted to remove. I selected all on the active worksheet and ran this and got an Compile Error: End If without Block If. I inserted a line before End Sub and put in an End If there, but it still didn't remove the elements. Any thoughts? Thanks, Jocelyn "Tom Ogilvy" wrote in message ... Sub deleteWithMultiple() Dim rng as Range, mycell as Range For Each myCell In Selection if mycell = "I" or mycell = "J" or mycell = "B" or _ mycell = "T" then if rng is nothing then set rng = mycell else set rng = Union(rng,mycell) end if End if Next myCell if not mycell is nothing then _ mycell.EntireRow.Delete End Sub -- Regards, Tom Ogilvy "Jocelyn" wrote in message om... Hi, Total newbie question here. I'd like some help with deleting entire rows based on multiple text conditions. In hunting around on the group, I snagged this macro, which will work for one text condition. How can I change it to make it select all cells on the sheet named Sheet2, look for multiple conditions and delete rows based on all of them in a single macro? Sub deleteWithI() For Each myCell In Selection If myCell = "I" Then myCell.EntireRow.Delete Next myCell End Sub TIA, Jocelyn |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
entirerow.delete multiple text conditions
Tom,
To simplify matters, I opened a brand-new workbook this morning, put the letters A to Z in Column A, and pasted the code you provided, exactly as written, in a new module. I returned to the workbook and ran the macro, and after I ran it, the I, J, B, and T were still there. I went back to the code and deleted the underscores, bringing those two long lines onto a single line, and tried again. Still no dice. It works for you, though? Any thoughts about what else I could be doing wrong in what seems like a pretty straightforward experiment? Thanks a million for your help and patience. Jocelyn "Tom Ogilvy" wrote in message ... As written, it compiles fine for me - so you must have screwed it up in some way when you made your changes. Perhaps if you post what you did, it can be corrected. -- Regards, Tom Ogilvy Jocelyn wrote in message om... Hi Tom - For some reason, this didn't work for me. I pasted the code into a module and replaced your I, J, B, & T with some of the elements I wanted to remove. I selected all on the active worksheet and ran this and got an Compile Error: End If without Block If. I inserted a line before End Sub and put in an End If there, but it still didn't remove the elements. Any thoughts? Thanks, Jocelyn "Tom Ogilvy" wrote in message ... Sub deleteWithMultiple() Dim rng as Range, mycell as Range For Each myCell In Selection if mycell = "I" or mycell = "J" or mycell = "B" or _ mycell = "T" then if rng is nothing then set rng = mycell else set rng = Union(rng,mycell) end if End if Next myCell if not mycell is nothing then _ mycell.EntireRow.Delete End Sub -- Regards, Tom Ogilvy "Jocelyn" wrote in message om... Hi, Total newbie question here. I'd like some help with deleting entire rows based on multiple text conditions. In hunting around on the group, I snagged this macro, which will work for one text condition. How can I change it to make it select all cells on the sheet named Sheet2, look for multiple conditions and delete rows based on all of them in a single macro? Sub deleteWithI() For Each myCell In Selection If myCell = "I" Then myCell.EntireRow.Delete Next myCell End Sub TIA, Jocelyn |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need Help with ActiveCell.EntireRow.Delete | Excel Discussion (Misc queries) | |||
Need Help with ActiveCell.EntireRow.Delete | Excel Discussion (Misc queries) | |||
delete entirerow if date more than 12months old | Excel Discussion (Misc queries) | |||
formula to fill text with multiple conditions | Excel Discussion (Misc queries) | |||
Combining Text from multiple cells under multiple conditions | Excel Worksheet Functions |