ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)
-   -   Delete Macro with OR/ELSE condition (https://www.excelbanter.com/excel-discussion-misc-queries/252969-delete-macro-else-condition.html)

Mike
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

Dave Peterson
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

Mike
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
.

Dave Peterson
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

Dave Peterson
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

Mike
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
.


All times are GMT +1. The time now is 06:47 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com