vba filter code
 

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

vba filter code
 

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.Dele te
End With
ActiveSheet.AutoFilterMode = False
Columns(4).Delete

End Sub

--
Regards,
Tom Ogilvy





"Gabriel" wrote in message
om...
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

vba filter code
 

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

Tom Ogilvy wrote:
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.Dele te
End With
ActiveSheet.AutoFilterMode = False
Columns(4).Delete

End Sub

vba filter code
 

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
column1) or b3 (on column2).

difference between "do not" and "do"

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

--
Regards,
Tom Ogilvy


"Tom Ogilvy" wrote in message
...
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.Dele te
End With
ActiveSheet.AutoFilterMode = False
Columns(4).Delete

End Sub

--
Regards,
Tom Ogilvy





"Gabriel" wrote in message
om...
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