vba filter code

  • Thread starter Thread starter Gabriel
  • Start date Start date
G

Gabriel

Hi,

I'm thinking to write a vba code in excel that filters a table based
on a precise criteria. For instance, I have a Table on Sheet1 that
looks like this:

a1 b1 c1
a2 b2 c2
a3 b3 c3

The macro should delete those rows that do not contain either a2 (on
column1) or b3 (on column2). In this example, the first row will be
the only one left.
Can anyone help ?

Thank you
Gabriel
 
The easiest would be to put in a helper column

Sub AAABB()
Dim rng As Range
Set rng = Range(Cells(1, 1), Cells(1, 1).End(xlDown))
rng.Offset(0, 3).Formula = "=if(Or(A1=""a2"",B1=""b3"")," & _
"""Keep"",""Delete"")"
Rows(1).Insert
Range("A1").Resize(1, 4).Value = Array("AA", "BB", "CC", "DD")
Range("A1").AutoFilter Field:=4, Criteria1:="Delete"
With ActiveSheet.AutoFilter.Range
.Columns(1).SpecialCells(xlVisible).EntireRow.Delete
End With
ActiveSheet.AutoFilterMode = False
Columns(4).Delete

End Sub
 
A different approach is available if the functions in the freely
downloadable file at http://home.pacbell.net/beban are available to your
workbook:

=ArrayRowFilter2(ArrayRowFilter1(Table_Range,1,"a2","<>"),2,"b3","<>")

array entered into a range of at least sufficient size to accommodate
the output.

Alan Beban
 
If you only want row 1 remaining, you would alter the helper column formula
to be


rng.Offset(0, 3).Formula = "=if(NOT(Or(A1=""a2"",B1=""b3""))," & _
"""Keep"",""Delete"")"

Your stated expectation, that only row 1 would remain, contradicts your
description of what you want deleted.

to match your expected results, I believe the condition would be to delete
any row that contains A2 in column 1 or B3 in column 2

to me, what you stated doesn't say that:
The macro should delete those rows that do not contain either a2 (on
Click to expand...
column1) or b3 (on column2).

difference between "do not" and "do"

Anyway, the code can be altered to do either as I showed.
 
Back
Top