Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Delete Macro with OR/ELSE condition
I am a beginner a writing Macros in Excel. I have a Macro that deletes any
record that does not equal "Server/Midrange Software" in column M. I need to expand this to include other criteria. Such as, if column M does not equal "Server/Midrange Software" or does not equal "Data Telecom" then delete the record. The Macro I have currently is..... Sub TryMe() 'Deletes rows where the value in column M are not equal to Server/Midrange Software Dim RowMdx As Long Dim LastRow As Long LastRow = Cells(Rows.Count, "M").End(xlUp).Row For RowMdx = LastRow To 1 Step -1 If Cells(RowMdx, "M").Value < "Server/Midrange Software" Then Rows(RowMdx).Delete End If Next RowMdx End Sub How do I put the OR condition in this for Data Telecom and possibly more conditions? Any help appreciated,,,,,,thanks -- Mike |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Delete Macro with OR/ELSE condition
If the number of options is small, you can use something like:
if lcase(Cells(RowMdx, "M").Value) = lcase("Server/Midrange Software") _ or lcase(Cells(RowMdx, "M").Value) = lcase("Data Telecom") _ or lcase(Cells(RowMdx, "M").Value) = lcase("some other value") _ 'keep it else rows(rowmdx).delete end if When the number of entries gets bigger, you may want to use a different approach... Dim res as variant dim myList as variant mylist = array("Server/Midrange Software", _ "Data Telecom", _ "some other value") .... for RowMdx = LastRow To 1 Step -1 res = application.match(cells(rowmdx,"M").value, mylist,0) if isnumber(res) then 'there was a match, skip it else Rows(RowMdx).Delete End If Next RowMdx The worksheet function =match() isn't case sensitive. Mike wrote: I am a beginner a writing Macros in Excel. I have a Macro that deletes any record that does not equal "Server/Midrange Software" in column M. I need to expand this to include other criteria. Such as, if column M does not equal "Server/Midrange Software" or does not equal "Data Telecom" then delete the record. The Macro I have currently is..... Sub TryMe() 'Deletes rows where the value in column M are not equal to Server/Midrange Software Dim RowMdx As Long Dim LastRow As Long LastRow = Cells(Rows.Count, "M").End(xlUp).Row For RowMdx = LastRow To 1 Step -1 If Cells(RowMdx, "M").Value < "Server/Midrange Software" Then Rows(RowMdx).Delete End If Next RowMdx End Sub How do I put the OR condition in this for Data Telecom and possibly more conditions? Any help appreciated,,,,,,thanks -- Mike -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Delete Macro with OR/ELSE condition
I tried this below but it deletes every record.
Sub TryMe() 'Deletes rows where the value in column M is not equal to Server/Midrange Software Dim RowMdx As Long Dim LastRow As Long LastRow = Cells(Rows.Count, "M").End(xlUp).Row For RowMdx = LastRow To 1 Step -1 If LCase(Cells(RowMdx, "M").Value) < LCase("Server/Midrange Software") _ Or LCase(Cells(RowMdx, "M").Value) < LCase("Data Telecom") Then Rows(RowMdx).Delete End If Next RowMdx End Sub When I use this it deletes only Server/Midrange Software Sub TryMe() 'Deletes rows where the value in column M is not equal to Server/Midrange Software Dim RowMdx As Long Dim LastRow As Long LastRow = Cells(Rows.Count, "M").End(xlUp).Row For RowMdx = LastRow To 1 Step -1 If Cells(RowMdx, "M").Value < "Server/Midrange Software" Then Rows(RowMdx).Delete End If Next RowMdx End Sub So I need obviously I am doing something wrong with the first Macro.. Any Help? -- Mike "Dave Peterson" wrote: If the number of options is small, you can use something like: if lcase(Cells(RowMdx, "M").Value) = lcase("Server/Midrange Software") _ or lcase(Cells(RowMdx, "M").Value) = lcase("Data Telecom") _ or lcase(Cells(RowMdx, "M").Value) = lcase("some other value") _ 'keep it else rows(rowmdx).delete end if When the number of entries gets bigger, you may want to use a different approach... Dim res as variant dim myList as variant mylist = array("Server/Midrange Software", _ "Data Telecom", _ "some other value") .... for RowMdx = LastRow To 1 Step -1 res = application.match(cells(rowmdx,"M").value, mylist,0) if isnumber(res) then 'there was a match, skip it else Rows(RowMdx).Delete End If Next RowMdx The worksheet function =match() isn't case sensitive. Mike wrote: I am a beginner a writing Macros in Excel. I have a Macro that deletes any record that does not equal "Server/Midrange Software" in column M. I need to expand this to include other criteria. Such as, if column M does not equal "Server/Midrange Software" or does not equal "Data Telecom" then delete the record. The Macro I have currently is..... Sub TryMe() 'Deletes rows where the value in column M are not equal to Server/Midrange Software Dim RowMdx As Long Dim LastRow As Long LastRow = Cells(Rows.Count, "M").End(xlUp).Row For RowMdx = LastRow To 1 Step -1 If Cells(RowMdx, "M").Value < "Server/Midrange Software" Then Rows(RowMdx).Delete End If Next RowMdx End Sub How do I put the OR condition in this for Data Telecom and possibly more conditions? Any help appreciated,,,,,,thanks -- Mike -- Dave Peterson . |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Delete Macro with OR/ELSE condition
Change the OR to AND.
Or use OR and Else like I did. Mike wrote: I tried this below but it deletes every record. Sub TryMe() 'Deletes rows where the value in column M is not equal to Server/Midrange Software Dim RowMdx As Long Dim LastRow As Long LastRow = Cells(Rows.Count, "M").End(xlUp).Row For RowMdx = LastRow To 1 Step -1 If LCase(Cells(RowMdx, "M").Value) < LCase("Server/Midrange Software") _ Or LCase(Cells(RowMdx, "M").Value) < LCase("Data Telecom") Then Rows(RowMdx).Delete End If Next RowMdx End Sub When I use this it deletes only Server/Midrange Software Sub TryMe() 'Deletes rows where the value in column M is not equal to Server/Midrange Software Dim RowMdx As Long Dim LastRow As Long LastRow = Cells(Rows.Count, "M").End(xlUp).Row For RowMdx = LastRow To 1 Step -1 If Cells(RowMdx, "M").Value < "Server/Midrange Software" Then Rows(RowMdx).Delete End If Next RowMdx End Sub So I need obviously I am doing something wrong with the first Macro.. Any Help? -- Mike "Dave Peterson" wrote: If the number of options is small, you can use something like: if lcase(Cells(RowMdx, "M").Value) = lcase("Server/Midrange Software") _ or lcase(Cells(RowMdx, "M").Value) = lcase("Data Telecom") _ or lcase(Cells(RowMdx, "M").Value) = lcase("some other value") _ 'keep it else rows(rowmdx).delete end if When the number of entries gets bigger, you may want to use a different approach... Dim res as variant dim myList as variant mylist = array("Server/Midrange Software", _ "Data Telecom", _ "some other value") .... for RowMdx = LastRow To 1 Step -1 res = application.match(cells(rowmdx,"M").value, mylist,0) if isnumber(res) then 'there was a match, skip it else Rows(RowMdx).Delete End If Next RowMdx The worksheet function =match() isn't case sensitive. Mike wrote: I am a beginner a writing Macros in Excel. I have a Macro that deletes any record that does not equal "Server/Midrange Software" in column M. I need to expand this to include other criteria. Such as, if column M does not equal "Server/Midrange Software" or does not equal "Data Telecom" then delete the record. The Macro I have currently is..... Sub TryMe() 'Deletes rows where the value in column M are not equal to Server/Midrange Software Dim RowMdx As Long Dim LastRow As Long LastRow = Cells(Rows.Count, "M").End(xlUp).Row For RowMdx = LastRow To 1 Step -1 If Cells(RowMdx, "M").Value < "Server/Midrange Software" Then Rows(RowMdx).Delete End If Next RowMdx End Sub How do I put the OR condition in this for Data Telecom and possibly more conditions? Any help appreciated,,,,,,thanks -- Mike -- Dave Peterson . -- Dave Peterson |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Delete Macro with OR/ELSE condition
Ps. Notice that I used OR and ELSE and a comparison operator of =.
I find that easier to understand. Dave Peterson wrote: Change the OR to AND. Or use OR and Else like I did. Mike wrote: I tried this below but it deletes every record. Sub TryMe() 'Deletes rows where the value in column M is not equal to Server/Midrange Software Dim RowMdx As Long Dim LastRow As Long LastRow = Cells(Rows.Count, "M").End(xlUp).Row For RowMdx = LastRow To 1 Step -1 If LCase(Cells(RowMdx, "M").Value) < LCase("Server/Midrange Software") _ Or LCase(Cells(RowMdx, "M").Value) < LCase("Data Telecom") Then Rows(RowMdx).Delete End If Next RowMdx End Sub When I use this it deletes only Server/Midrange Software Sub TryMe() 'Deletes rows where the value in column M is not equal to Server/Midrange Software Dim RowMdx As Long Dim LastRow As Long LastRow = Cells(Rows.Count, "M").End(xlUp).Row For RowMdx = LastRow To 1 Step -1 If Cells(RowMdx, "M").Value < "Server/Midrange Software" Then Rows(RowMdx).Delete End If Next RowMdx End Sub So I need obviously I am doing something wrong with the first Macro.. Any Help? -- Mike "Dave Peterson" wrote: If the number of options is small, you can use something like: if lcase(Cells(RowMdx, "M").Value) = lcase("Server/Midrange Software") _ or lcase(Cells(RowMdx, "M").Value) = lcase("Data Telecom") _ or lcase(Cells(RowMdx, "M").Value) = lcase("some other value") _ 'keep it else rows(rowmdx).delete end if When the number of entries gets bigger, you may want to use a different approach... Dim res as variant dim myList as variant mylist = array("Server/Midrange Software", _ "Data Telecom", _ "some other value") .... for RowMdx = LastRow To 1 Step -1 res = application.match(cells(rowmdx,"M").value, mylist,0) if isnumber(res) then 'there was a match, skip it else Rows(RowMdx).Delete End If Next RowMdx The worksheet function =match() isn't case sensitive. Mike wrote: I am a beginner a writing Macros in Excel. I have a Macro that deletes any record that does not equal "Server/Midrange Software" in column M. I need to expand this to include other criteria. Such as, if column M does not equal "Server/Midrange Software" or does not equal "Data Telecom" then delete the record. The Macro I have currently is..... Sub TryMe() 'Deletes rows where the value in column M are not equal to Server/Midrange Software Dim RowMdx As Long Dim LastRow As Long LastRow = Cells(Rows.Count, "M").End(xlUp).Row For RowMdx = LastRow To 1 Step -1 If Cells(RowMdx, "M").Value < "Server/Midrange Software" Then Rows(RowMdx).Delete End If Next RowMdx End Sub How do I put the OR condition in this for Data Telecom and possibly more conditions? Any help appreciated,,,,,,thanks -- Mike -- Dave Peterson . -- Dave Peterson -- Dave Peterson |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Delete Macro with OR/ELSE condition
Dave, thanks a bunch, the OR worked great...I should have know that from my
old COBOL days but didn't think of it..... Thanks again -- Mike "Dave Peterson" wrote: Change the OR to AND. Or use OR and Else like I did. Mike wrote: I tried this below but it deletes every record. Sub TryMe() 'Deletes rows where the value in column M is not equal to Server/Midrange Software Dim RowMdx As Long Dim LastRow As Long LastRow = Cells(Rows.Count, "M").End(xlUp).Row For RowMdx = LastRow To 1 Step -1 If LCase(Cells(RowMdx, "M").Value) < LCase("Server/Midrange Software") _ Or LCase(Cells(RowMdx, "M").Value) < LCase("Data Telecom") Then Rows(RowMdx).Delete End If Next RowMdx End Sub When I use this it deletes only Server/Midrange Software Sub TryMe() 'Deletes rows where the value in column M is not equal to Server/Midrange Software Dim RowMdx As Long Dim LastRow As Long LastRow = Cells(Rows.Count, "M").End(xlUp).Row For RowMdx = LastRow To 1 Step -1 If Cells(RowMdx, "M").Value < "Server/Midrange Software" Then Rows(RowMdx).Delete End If Next RowMdx End Sub So I need obviously I am doing something wrong with the first Macro.. Any Help? -- Mike "Dave Peterson" wrote: If the number of options is small, you can use something like: if lcase(Cells(RowMdx, "M").Value) = lcase("Server/Midrange Software") _ or lcase(Cells(RowMdx, "M").Value) = lcase("Data Telecom") _ or lcase(Cells(RowMdx, "M").Value) = lcase("some other value") _ 'keep it else rows(rowmdx).delete end if When the number of entries gets bigger, you may want to use a different approach... Dim res as variant dim myList as variant mylist = array("Server/Midrange Software", _ "Data Telecom", _ "some other value") .... for RowMdx = LastRow To 1 Step -1 res = application.match(cells(rowmdx,"M").value, mylist,0) if isnumber(res) then 'there was a match, skip it else Rows(RowMdx).Delete End If Next RowMdx The worksheet function =match() isn't case sensitive. Mike wrote: I am a beginner a writing Macros in Excel. I have a Macro that deletes any record that does not equal "Server/Midrange Software" in column M. I need to expand this to include other criteria. Such as, if column M does not equal "Server/Midrange Software" or does not equal "Data Telecom" then delete the record. The Macro I have currently is..... Sub TryMe() 'Deletes rows where the value in column M are not equal to Server/Midrange Software Dim RowMdx As Long Dim LastRow As Long LastRow = Cells(Rows.Count, "M").End(xlUp).Row For RowMdx = LastRow To 1 Step -1 If Cells(RowMdx, "M").Value < "Server/Midrange Software" Then Rows(RowMdx).Delete End If Next RowMdx End Sub How do I put the OR condition in this for Data Telecom and possibly more conditions? Any help appreciated,,,,,,thanks -- Mike -- Dave Peterson . -- Dave Peterson . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Delete Row with condition | Excel Discussion (Misc queries) | |||
Macro to delete rows based on a condition | Excel Worksheet Functions | |||
How to delete lines programmatically if a condition is met? | Excel Discussion (Misc queries) | |||
Is there a way to delete a cell value based on a condition? | Excel Discussion (Misc queries) | |||
Can I delete an entire row if condition is not met? | Excel Worksheet Functions |