Page 1 of 3 1 2 3 LastLast
Results 1 to 10 of 23

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

  1. #1

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



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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

  2. #2
    Quote Originally Posted by Sand View Post

    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

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

  4. #4
    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

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

  6. #6

    Almost there...

    Thanks so much for the help!

    Quote Originally Posted by Bob Phillips View Post
    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

  7. #7
    Something like

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

  8. #8
    Quote Originally Posted by Jesse View Post
    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

  9. #9
    Quote Originally Posted by Bob Phillips View Post
    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

  10. #10
    Quote Originally Posted by Sand View Post
    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.

    Quote Originally Posted by Sand View Post
    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.

Page 1 of 3 1 2 3 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •