Copy a range based on a filtered (drop-down list)

Sand

New member
Joined
Apr 6, 2011
Messages
13
Reaction score
0
Points
0
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
 
Results are copied to "MyResultSheet" and begin on Row 2

****Begin code****
Sheets("MyReportSheet").Select
****End code****

Obviously, "MyResultSheet" in the notes should be "MyReportSheet" as in the code. Sorry for the oversight.
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")
 
Almost there...

Thanks so much for the help!

Correction

Code:
On Error GoTo 0
If Not rng Is Nothing Then rng.Entirerow.Copy Sheets("MyReportSheet").Range("A2")

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")...
 
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

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?

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

Something like that?

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
 
Something like

Code:
rng.Entirerow.Columns("C:D")...

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
 
That does work, thank you! I will test it more extensively to make sure, but looks like that might do the trick.

Actually, not quite working yet. Still trying to figure out why. Doesn't seem to be copying all the visible data. It works with the first entry, but not all of them.

As an aside, why should I not just use your first code? What is wrong with NOT using the EntireRow parameter?

Delete that question for stupidity, sorry.
 
The array might be interesting to play with, but it will be much slower to load than the Specialcells method.
 
The array might be interesting to play with, but it will be much slower to load than the Specialcells method.

I see. Well, speed is not terribly important for this workbook, as it is only 200 rows by 20 columns, but I do see your point.

However, I am seeing strange results with the Specialcells method. It does not appear to display all of the filtered values. Also, when running it through more than once (to fill all three columns in the result sheet, for instance), if the category was empty and the one before it had data, the display would repeat the entry before. A simple "Set rng = Nothing" before each new criteria took care of that, but I'm still seeing "interesting" results.

In some of the columns, I only get 1 value, while in others, I get 2 or 3. The code is identical; the only changes were to the criteria selected and the column to display the results. It must be something I'm doing, but I haven't figured it out yet.

But regardless, the routine is NOT displaying all visible data, as most of the columns should have 5 or more entries. Hrmmm...
 
Specialcells doesn't display anything, it is the filter that controls that. All that Specialcells does is to limit to range that satisfies the Specialcells property, visible in this case.
 
Specialcells doesn't display anything, it is the filter that controls that. All that Specialcells does is to limit to range that satisfies the Specialcells property, visible in this case.

Okay, I get that. Here is what is working.

Code:
sub test()
 
    Dim rng As Range

    'clear the result sheet because the categories could change daily
    Sheets("MyResultsSheet").Select
    Range("A4:K200").Select
    Selection.Clear

 
    'Clear any filter that happens to be set on the Main Sheet    
    Sheets("MyMainSheet").Select
        On Error Resume Next
        ActiveSheet.ShowAllData
        On Error GoTo 0
    
    'Clear any leftover data in "rng" variable
    Set rng = Nothing
    
    'Filter Data Table on criteria ("Cat3" in this case)
    ActiveSheet.ListObjects("MyDataTable").Range.AutoFilter Field:=13, Criteria1:= _
        "Cat3"
 
    'Set rng to the visible cells I want, in this case A2:A200
    On Error Resume Next
    Set rng = Range("A2:A200").SpecialCells(xlCellTypeVisible)
    On Error GoTo 0
    
    'Display the results on the Results Sheet beginning in the proper cell
    If Not rng Is Nothing Then rng.Copy Sheets("MyResultSheet").Range("C4")

End sub

I just re-run the code (changed appropriately, of course) for each different category I want to sort on.

I realize that setting the exact range limits the usefulness of the routine (I will have to modify all the code if the table ever expands to larger than 200 rows), but for some reason I could not make the other code work. I'm sure I must be using improper syntax due to my inexperience. I'll continue to work on it; hopefully I can debug it.

In the meantime, this is working, so thanks a bunch!
Sand
 
Well I am glad it is working for you, but I still don't get why you have to use A2:A200 and can't just use the table name. That is what I did n my tests.
 
Well I am glad it is working for you, but I still don't get why you have to use A2:A200 and can't just use the table name. That is what I did n my tests.

Indeed, that was very strange. I tested it against several "categories" at once; about 10 or so. I just copied the same code over ten times in the macro and just changed the filter criteria and where the results were to be displayed. On most of them, only the first cell of filtered data was displayed in the results column, like this:

|__CAT1___|
|_Data Bit1_|
|__________|

When there were actually 5 items in that category (and every other except 3). The really weird part, however, was that a couple of the results columns had TWO items listed and one had THREE. I made sure all the test categories had 4 or more items, however, except the two with zero items to check that. So, even the discrepancy wasn't standard across all of the data. The only thing that was the same across all result columns is that none of them displayed all of the data. I will have to play with it more. I know the table name will work; I use it a lot in other macros and it does just fine. If I can't figure it out, I'll post a sample file.
 
Last edited:
You could also try using a dynamic range so as not to hardcode it

Code:
Set rng = Range("OFFSET(A2,,,COUNT(A:A)-1,1)").SpecialCells(xlCellTypeVisible)
 
You could also try using a dynamic range so as not to hardcode it

Code:
Set rng = Range("OFFSET(A2,,,COUNT(A:A)-1,1)").SpecialCells(xlCellTypeVisible)

Good idea. Tried it but I get an error "info cannot be pasted because copy and paste area are not the same size and shape".

I want to begin the paste at the fourth row; "B4" for example, so here is the code I used:

Code:
    On Error Resume Next
'    Set rng = Range("A2:A200").SpecialCells(xlCellTypeVisible)
    Set rng = Range("OFFSET(A2,,,COUNT(A:A)-1,1)").SpecialCells(xlCellTypeVisible)
    On Error GoTo 0
 
    If Not rng Is Nothing Then rng.Copy Sheets("MyResultSheet").Range("B4")

The commented line is what works with the rest of the code as written. I'm sure this is a syntax error on my part. How should I define the paste range?
 
Code:
If Not rng Is Nothing Then rng.Copy Sheets("MyResultSheet").Range("B4")

If I try this instead:

Code:
If Not rng Is Nothing Then rng.Copy Sheets("MyResultSheet").Range("B4:B")

the error changes to "Run-time error '1004' Application-defined or object-defined error" and highlights everything behind the "Then"
 
Hi Sandi,

This is written in the browser and not tested, but how about:
Code:
If Not rng Is Nothing Then
rng.Copy
Sheets("MyResultSheet").Range("B4").PasteSpecial paste:=xlpasteall
End If
 
Back
Top