All,
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:
Notes:
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
****Begin code****
Sheets("MyMainSheet").Select
ActiveSheet.ListObjects("MyDataTable").Range.AutoFilter Field:=13, Criteria1:= _
"CAT 1"
Range("A2:A200").Select
Selection.Copy
Sheets("MyReportSheet").Select
Range("A2").Select
ActiveSheet.Paste
****End code****
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.
Sand
Try this
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?
Jesse
Correction
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 "Not rng..." line works better. This one errors out because the copy range is not the same size and shape as the destination range. (I used the code with a destination that was not A2...)
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!
Sand
Something like
Code:rng.Entirerow.Columns("C:D")...
Thanks for the suggestion! I have played with arrays a little bit, and figured out how to display the contents of an array in a column using WorksheetFunction.Transpose(myArray), but I haven't figured out how to load the array with data that is dynamic; i.e., the number of units in each category could change from day to day.
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... ;-)
Sand
That does work, thank you! I will test it more extensively to make sure, but looks like that might do the trick.
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!
Sand
Bookmarks