Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Filter Date Code for macro | Excel Discussion (Misc queries) | |||
Code to replace Advanced Filter | Excel Discussion (Misc queries) | |||
Filter isn't working with a VBA Code | Excel Worksheet Functions | |||
Apply more than one filter in macro/vb code | Excel Programming | |||
Is it posible to set up some code to Filter Worksheet on Colours | Excel Programming |