Filtering Problem in VBA

[Momchil]

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>

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

 

[DonkeyOte]

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

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

 

[Momchil]

Thanks very much, lasw10!

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

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:

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

 

[DonkeyOte]

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:

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

 

[Momchil]

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!