VBA for filtering columns

Question

I have a big database-like sheet, first row contains headers. I would like a subset of rows of this table based on column values. Two issues:

1) VBA-wise I would like to loop through the columns, when the values for all necessary columns all match, copy the entire row into a new sheet.

2) The subset of rows is based on a list. I just read I can use Autofilter with an array. Is it possible to input this array from a column instead of manually entering it in the VBA code? The list I'm using consists of 200 different strings and will be updated periodically.

Where CritList is the list of strings. I still need to figure out how, but now I leave the office, so more tomorrow.

EDIT1 Thanks to @DougGlancy; the autofiltering works now. Here is his beautiful code (I only added the array-filter).

EDIT2 Included a more elaborate array-filter, where NameList is the list I would like to filter for. Now it all works!

Sub FilterAndCopy()
Dim LastRow As Long

Dim vName As Variant
Dim rngName As Range
Set rngName = Sheets("Sheet3").Range("NameList")

vName = rngName.Value

Sheets("Sheet2").UsedRange.Offset(0).ClearContents
With Worksheets("Sheet1")
    .Range("A:E").AutoFilter

    'Array filter from NameList
    .Range("A:J").AutoFilter Field:=3, Criteria1:=Application.Transpose(vName), _
                                Operator:=xlFilterValues

    .Range("A:E").AutoFilter field:=2, Criteria1:="=String1" _
                                  , Operator:=xlOr, Criteria2:="=string2"
    .Range("A:E").AutoFilter field:=3, Criteria1:=">0", _
    .Range("A:E").AutoFilter field:=5, Criteria1:="Number"

    LastRow = .Range("A" & .Rows.Count).End(xlUp).Row
    .Range("A1:A" & LastRow).SpecialCells(xlCellTypeVisible).EntireRow.Copy _
            Destination:=Sheets("Sheet2").Range("A1")

End With
End Sub

How about using an autofilter instead? Which version of excel are you using? — nutsch, Dec 18 '12 at 15:29
I'm using Excel 2011 on a Mac. Yes. let's see if I get the autofilter working.. — Ampi Severe, Dec 18 '12 at 16:15

Doug Glancy · Accepted Answer · 2012-12-18T17:38:04.757

16

Here's a different approach. The heart of it was created by turning on the Macro Recorder and filtering the columns per your specifications. Then there's a bit of code to copy the results. It will run faster than looping through each row and column:

Sub FilterAndCopy()
Dim LastRow As Long

Sheets("Sheet2").UsedRange.Offset(0).ClearContents
With Worksheets("Sheet1")
    .Range("$A:$E").AutoFilter
    .Range("$A:$E").AutoFilter field:=1, Criteria1:="#N/A"
    .Range("$A:$E").AutoFilter field:=2, Criteria1:="=String1", Operator:=xlOr, Criteria2:="=string2"
    .Range("$A:$E").AutoFilter field:=3, Criteria1:=">0"
    .Range("$A:$E").AutoFilter field:=5, Criteria1:="Number"
    LastRow = .Range("A" & .Rows.Count).End(xlUp).Row
    .Range("A1:A" & LastRow).SpecialCells(xlCellTypeVisible).EntireRow.Copy _
            Destination:=Sheets("Sheet2").Range("A1")
End With
End Sub

As a side note, your code has more loops and counter variables than necessary. You wouldn't need to loop through the columns, just through the rows. You'd then check the various cells of interest in that row, much like you did.

edited Dec 18 '12 at 17:38

answered Dec 18 '12 at 15:31

Doug Glancy

27,214
6
67
115

Thank you very much, but it doesn't seem to work (or I explained myself wrong). I've run the code with two criteria, the "String" one and the ">0" one. I still see rows with a different string in "2" and I still see rows with 0 in "3". (And what is the E:E filter for?) – Ampi Severe Dec 18 '12 at 15:59
Oh it works now! Apparently the "E:E" filter is very important :-) One more issue to solve (but as soon as that happens I'll accept your answer). – Ampi Severe Dec 18 '12 at 16:42
1

You're right, it is important. It's always a little confusing to me, but my theory is that filter makes it all work if you start with or without a filter, in those columns or somewhere else. It actually should be "A:A", just for better clarity, and I'll edit it to be so. – Doug Glancy Dec 18 '12 at 16:57
Actually, I also see that I forgot to clean up the other filter ranges. I'll edit in a minute. – Doug Glancy Dec 18 '12 at 16:58
1

OK, that seems to work whether I start with the correct filter already in place, no filter, or a filter somewhere else in the sheet. – Doug Glancy Dec 18 '12 at 17:05
Thanks, and one more question, why the "And" operator between filter 3 and 5? – Ampi Severe Dec 18 '12 at 17:08
No good reason, it's a vestige of the Macro Recorder. I deleted it. – Doug Glancy Dec 18 '12 at 17:38
2

Just a small alternative: instead of storing all the filters in VBA, apply them in column F using AND and OR functions. Then only filter for TRUE in this column using VBA. This way, the filtering logic which is most likely representing some kind of business logic can be understood even without looking at the VBA code... – Peter Albert Dec 18 '12 at 19:56
@PeterAlbert, what an excellent suggestion! I would certainly do it that way in practice, but got lost in the code details here. – Doug Glancy Dec 18 '12 at 20:30
@PeterAlbert: That sounds like a very good idea, but I am not sure I completely understand. Could you give me an example? – Ampi Severe Dec 19 '12 at 08:52
2

Sure: In cell F2, use the following formula: `=AND(ISNA(A2),OR(B2="String1",B2="String2"),C2>=0,E2="Number")*1` and copy it into column F. Then, adjust the VBA code above to include all lines starting with `.Range("$A:$E").AutoFilter` to `.Range("$A:$F").AutoFilter .Range("$A:$F").AutoFilter Field:=6, Criteria1:="1"` – Peter Albert Dec 19 '12 at 21:30

VBA for filtering columns

1 Answers1

Linked