Filtering Problem in VBA

Momchil

New Member
Joined
Jul 15, 2008
Messages
6
Hello Everyone,

I am new to this forum and I tried to find a similar thread, but couldn't find one addressing my issue exactly.

I am using Excel 2003 and I have a worksheet that contains about 3000 rows and is updated daily.
I filter the worksheet based on different criteria and save a number of smaller files.

The problem I have here is the following:
In columns N and O, I have different values. What I have to do there is to find in both columns cells that contain 2 certain values and delete all the rest of the rows. So, for example if the values are "123" and "234" I need to find all cells in columns N and O that contain those values and delete the rest of the rows. SO, I need to remove rows where in both columns the value is different than the two above.

Maybe there is a better way to do this but at the moment I am trying to do this using the following code (I am starting to wonder if it is logically correct):

<CODE>
</CODE>
Code:
Dim LastRow As Long
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
For i = LastRow To 2 Step -1
 
If Cells(i, "N").Value <> "123" Or _
Cells(i, "N").Value <> "234" And _
Cells(i, "O").Value <> "123" Or _
Cells(i, "O").Value <> "234" Then
Rows(i).EntireRow.Delete
End If
Next i
<CODE></CODE>

When trying to run this code, I receive a 'Type Mismatch" error.

I would really appreciate any help on this, or a suggestion how to perform this filtering in another way.

Thanks very much!

Best regards,
Momchil
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
Welcome to the Board.

If you want to delete rows where either of N/O do not contain 123/234 and where N/O are not the same (ie both 123/123 or 234/234) ...

Code:
Dim LastRow As Long
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
For i = LastRow To 2 Step -1
If (CDbl(Cells(i, "N").Value) <> 123 And CDbl(Cells(i, "N").Value) <> 234) _
    Or CDbl((Cells(i, "O").Value) <> 123 And CDbl(Cells(i, "O").Value) <> 234) _
    Or CDbl(Cells(i, "N").Value) = CDbl(Cells(i, "O").Value) Then
    Rows(i).EntireRow.Delete
End If
Next i

If duplication is ok (ie can both be 123 or both be 234) then remove the final OR condition.

HTH
 
Upvote 0
Thanks very much, lasw10!

Duplication is OK, so I removed the last OR condition as you advised.

Code:
Dim LastRow As Long
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
For i = LastRow To 2 Step -1
If (CDbl(Cells(i, "N").Value) <> 123 And CDbl(Cells(i, "N").Value) <> 234) _
    Or CDbl((Cells(i, "O").Value) <> 123 And CDbl(Cells(i, "O").Value) <> 234) Then
    Rows(i).EntireRow.Delete
End If
Next i

but I still receive the Type Mismatch error !?!

The debugger highlights the following:

Code:
If (CDbl(Cells(i, "N").Value) <> 123 And CDbl(Cells(i, "N").Value) <> 234) _
    Or CDbl((Cells(i, "O").Value) <> 123 And CDbl(Cells(i, "O").Value) <> 234) Then

I double checked column names, values - everythings seems ok...

Generally what would be the reason for a type mismatch error?

Thanks very much,
Momchil
 
Upvote 0
Normally incompatible types :) ... though there is a typo in the code I gave you which won't help... just in case some of the values in N/O are non-numeric (ie hello rather than 123) probably best to use something like:

Code:
Dim LastRow As Long
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
For i = LastRow To 2 Step -1
If (CStr(Cells(i, "N").Value) <> "123" And CStr(Cells(i, "N").Value) <> "234") _
    Or (CStr(Cells(i, "O").Value) <> "123" And CStr(Cells(i, "O").Value) <> "234") Then
    Rows(i).EntireRow.Delete
End If
Next i
 
Upvote 0
Great :)

Amazing, this one works!

You are right - the values in this two columns come from a previously run vlookup (the cells contain just the values though, no formulas) and there are values like "#" there, so probably this has been the problem.

Thanks very much, lasw10! :)
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,953
Members
448,535
Latest member
alrossman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top