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! |
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! |
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! |
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! |
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! |
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! |
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! |
All times are GMT +1. The time now is 10:33 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com