Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Once criteria has been met, delet the same row.
Good day,
I am Looping down the first column of a spreadsheet and checking for a certain bit of text in the 3rd column, when i loop downwards and i find what i am looking for, i want to delete the entire row with the data that has met my requirements, can anybody help me with some code? I also have multiple criteria that i must look for, is it possible to use wild cards? Example: I have 10 different combinations, all starting with "H" Do Until ActiveCell = "" If ActiveCell.Offset(0, 2) = ("HEP") Then ElseIf ActiveCell.Offset(0, 2) = ("HEPA") Then Les Stout *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Once criteria has been met, delet the same row.
BTW You don't need the ()
If ActiveCell.Offset(, 2) = "HEP*" Then "Les Stout" wrote in message ... Good day, I am Looping down the first column of a spreadsheet and checking for a certain bit of text in the 3rd column, when i loop downwards and i find what i am looking for, i want to delete the entire row with the data that has met my requirements, can anybody help me with some code? I also have multiple criteria that i must look for, is it possible to use wild cards? Example: I have 10 different combinations, all starting with "H" Do Until ActiveCell = "" If ActiveCell.Offset(0, 2) = ("HEP") Then ElseIf ActiveCell.Offset(0, 2) = ("HEPA") Then Les Stout *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Once criteria has been met, delet the same row.
Hi Nick/Don,
Thanks very much for the help, I really appreciate it. Nick, I also have the following and have tried to change the code to suit but I cannot get it to work, Heeeelp ?? Like previously mentioned, I have1 10 starting with “H” and the rest are, they must all be deleted; AEPA, ASPA, AEPE, AEPR, ALPA, AZPA, AZPE ,LA ,LG , LU & NB. I don’t think i understand the language, as I tried changing the code by changing “H” to “*PA”, but it still did not work, has it something to do with the for x ? Can you use the wild card with as well? As stated, I new to the game, but love it and very frustrated at this stage, as I know what I want to do, but can’t !! Les Stout *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Once criteria has been met, delet the same row.
Nick's code would delete the row IF the text in col C started with H or h.
If that is not what you want ask again. "Les Stout" wrote in message ... Hi Nick/Don, Thanks very much for the help, I really appreciate it. Nick, I also have the following and have tried to change the code to suit but I cannot get it to work, Heeeelp ?? Like previously mentioned, I have1 10 starting with "H" and the rest are, they must all be deleted; AEPA, ASPA, AEPE, AEPR, ALPA, AZPA, AZPE ,LA ,LG , LU & NB. I don't think i understand the language, as I tried changing the code by changing "H" to "*PA", but it still did not work, has it something to do with the for x ? Can you use the wild card with as well? As stated, I new to the game, but love it and very frustrated at this stage, as I know what I want to do, but can't !! Les Stout *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Once criteria has been met, delet the same row.
Sub deleterows()
Dim lLastRow As Long Dim x As Long Dim arr as Variant Dim i as long arr = Array("AEPA", "ASPA", "AEPE", "AEPR", _ "ALPA", "AZPA", "AZPE" ,"LA" ,"LG" , "LU", "NB") lLastRow = Range("A65536").End(xlUp).Row For x = lLastRow To 1 Step -1 If UCase(Left(Range("A" & x).Offset(0, 2).Value, 1)) = "H" Then Range("A" & x).EntireRow.Delete Else for i = lbound(arr) to ubound(arr) if Instr(1,Range("A" & x).Offset(0,2).Value,arr(i),vbTextCompare) Then Range("A" & x).EntireRow.Delete Exit for end if Next End If Next x End Sub "Les Stout" wrote in message ... Hi Nick/Don, Thanks very much for the help, I really appreciate it. Nick, I also have the following and have tried to change the code to suit but I cannot get it to work, Heeeelp ?? Like previously mentioned, I have1 10 starting with "H" and the rest are, they must all be deleted; AEPA, ASPA, AEPE, AEPR, ALPA, AZPA, AZPE ,LA ,LG , LU & NB. I don't think i understand the language, as I tried changing the code by changing "H" to "*PA", but it still did not work, has it something to do with the for x ? Can you use the wild card with as well? As stated, I new to the game, but love it and very frustrated at this stage, as I know what I want to do, but can't !! Les Stout *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Once criteria has been met, delet the same row.
Hi Don,
as i treid to explain, i do want to delete the "H" parts, but the others as listed in my last mail also Les Stout *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Once criteria has been met, delet the same row.
Tom
Thanks...that saved some of my brain cells. Hopefully it works for you Les -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England HIS Tom Ogilvy wrote: Sub deleterows() Dim lLastRow As Long Dim x As Long Dim arr as Variant Dim i as long arr = Array("AEPA", "ASPA", "AEPE", "AEPR", _ "ALPA", "AZPA", "AZPE" ,"LA" ,"LG" , "LU", "NB") lLastRow = Range("A65536").End(xlUp).Row For x = lLastRow To 1 Step -1 If UCase(Left(Range("A" & x).Offset(0, 2).Value, 1)) = "H" Then Range("A" & x).EntireRow.Delete Else for i = lbound(arr) to ubound(arr) if Instr(1,Range("A" & x).Offset(0,2).Value,arr(i),vbTextCompare) Then Range("A" & x).EntireRow.Delete Exit for end if Next End If Next x End Sub "Les Stout" wrote in message ... Hi Nick/Don, Thanks very much for the help, I really appreciate it. Nick, I also have the following and have tried to change the code to suit but I cannot get it to work, Heeeelp ?? Like previously mentioned, I have1 10 starting with "H" and the rest are, they must all be deleted; AEPA, ASPA, AEPE, AEPR, ALPA, AZPA, AZPE ,LA ,LG , LU & NB. I don't think i understand the language, as I tried changing the code by changing "H" to "*PA", but it still did not work, has it something to do with the for x ? Can you use the wild card with as well? As stated, I new to the game, but love it and very frustrated at this stage, as I know what I want to do, but can't !! Les Stout *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to delet the blank rows in whole sheet at a time | Excel Worksheet Functions | |||
Delet Duplicate Blanks rows | Excel Worksheet Functions | |||
delet multiple entries on same row(different columns) | Excel Discussion (Misc queries) | |||
DELET PERTICULER WORD FROM THE EXCEL SHEET | Excel Worksheet Functions | |||
how do I lock formulas, but still allow rows to be added or delet. | Excel Worksheet Functions |