Sorry if this has been answered before; I didn't know quite what to search for.
I am using VBA to copy a range based on filter results from a drop-down list. My problem is, if the there are no results from the drop-down list, the copy returns the ENTIRE range when I should think it would return nothing.
For example: a drop down list contains 3 choices, Cat1, Cat2, and Cat3. All row data is tagged to one of those choices. I use VBA to filter on that column and return a report that lists of all data in the different Categories, as such:
|___CAT 1___|___CAT 2___|___CAT 3___|
However, if all my data is in either CAT2 or CAT3 and nothing is tagged as CAT1, then the entire dataset is returned in the CAT 1 column on the results page.
Below is the code I was using:
Main datasheet is "MyMainSheet" and table is "MyDataTable"
The 13th field is the drop-down list (Cat 1, Cat 2, Cat 3)
There are 199 rows of data starting on row 2
Results are copied to "MyResultSheet" and begin on Row 2
ActiveSheet.ListObjects("MyDataTable").Range.AutoFilter Field:=13, Criteria1:= _
In the above example, if no row is tagged as Cat 1, the results returned are the entire data range in column A (as if no filter had been applied) instead of a blank column A on the results page.
What am I missing?
Thanks in advance for any suggestions.
Code:Dim rng As Range Sheets("MyMainSheet").Select ActiveSheet.ListObjects("MyDataTable").Range.AutoFilter Field:=13, Criteria1:= _ "CAT 1" On Error Resume Next Set rng = Range("MyDataTable").SpecialCells(xlCellTypeVisible) On Error GoTo 0 If Not rng Is Nothing Then rng.Copy Sheets("MyReportSheet").Range("A2")
If you're pulling filtered data out of a list like this would it be better to filter and copy or step through the entries, load the matches in an array and write them to the output location?
Code:Dim rng As Range Sheets("MyMainSheet").Select ActiveSheet.ListObjects("MyDataTable").Range.AutoFilter Field:=13, Criteria1:= _ "CAT 1" On Error Resume Next Set rng = Range("MyDataTable").SpecialCells(xlCellTypeVisible) On Error GoTo 0 If Not rng Is Nothing Then rng.Entirerow.Copy Sheets("MyReportSheet").Range("A2")
Thanks so much for the help!
Your first code did copy all the row data from the selection--if there was any data tagged as CAT 1--and did leave the column blank if there was not.
The "EntireRow.Copy" technique will be useful for me in another part of the workbook, I think. However, in the results table, I JUST need data from the one column ("A" in my example) to be displayed under each of the category headings. I'm sorry, I should have said.
Is there a modification that would allow me to only copy just the one column? Or, alternately, allow me to copy selected columns along the row?
Thanks again, I really appreciate the time you're spending teaching me!
Maybe this is what you are saying anyway, but couldn't I load the ENTIRE range into the array and then filter and display the results on the fly, per Category (by using For/Next loops to step through the array, for example)? If so, then I guess I would need to figure out how to load a two-dimensional array with both the category and the unit name columns, and then use For/Next to filter by Category, as such?
Something like that?Code:For x = LBound(MyArray) To UBound(MyArray) arFiltered = Filter(MyArray, "CAT1") Range("A1:A10").Select Range("A1:A10").Value = Application.WorksheetFunction.Transpose(arFiltered) Next x
Now if I can only figure out how to load the proper column values into the array, and change the A1:A10 range to dynamic, I'll be all set. Maybe... ;-)
I am still going to try to figure out how to use an array, though, as a learning experience.
As an aside, why should I not just use your first code? What is wrong with NOT using the EntireRow parameter?
Thanks again for the help!