none
Filter and select /copy range RRS feed

  • Question

  • Hi

    I have a list (say with 100 rows) and I want to write code to filter this on certain values, and then copy the filtered data to another sheet.

    I can write the code to filter but am struggling with the code to select the filtered data and copy. 

    As an example:

    Say, I filtered all the items in column A that had a value of "Green" , I might find that the select rows are  10,11,15,18,25 (5 rows in total)

    If then filtered on in Column A for value "Red", I would get 15 rows (not necessarily contingent)

    So the problem is : how can I find how many lines are selected with Green and then with Red. I want to be able to set a Range to enable me to copy. I know that can use the rows count feature to get the last line (in the green example that would be 25) but I cannot find out what the first selected value would be.

    I hope this makes sense - any help would be appreciated.

    thank you.

    Monday, May 18, 2015 1:06 PM

Answers

  • I want to be able to set a Range to enable me to copy.

    Get the visible cells of the Autofilter Range.

    set R = ActiveSheet.AutoFilter.Range.SpecialCells(xlCellTypeVisible)

    Andreas.

    Monday, May 18, 2015 3:08 PM
  • Suppose you are working with Range("A1:A100") and already applied filter (By VBA or manually) .

    Range("A1:A100").Specialcells(xlvisible).Copy

    The above will copy the filtered range.


    Best Regards,
    Asadulla Javed, Kolkata
    ---------------------------------------------------------------------------------------------
    Please do not forget to click “Vote as Helpful” if any post helps you and "Mark as Answer”if it solves the issue.

    • Marked as answer by py1 Monday, May 18, 2015 3:38 PM
    Monday, May 18, 2015 3:10 PM
    Answerer

All replies

  • I want to be able to set a Range to enable me to copy.

    Get the visible cells of the Autofilter Range.

    set R = ActiveSheet.AutoFilter.Range.SpecialCells(xlCellTypeVisible)

    Andreas.

    Monday, May 18, 2015 3:08 PM
  • Suppose you are working with Range("A1:A100") and already applied filter (By VBA or manually) .

    Range("A1:A100").Specialcells(xlvisible).Copy

    The above will copy the filtered range.


    Best Regards,
    Asadulla Javed, Kolkata
    ---------------------------------------------------------------------------------------------
    Please do not forget to click “Vote as Helpful” if any post helps you and "Mark as Answer”if it solves the issue.

    • Marked as answer by py1 Monday, May 18, 2015 3:38 PM
    Monday, May 18, 2015 3:10 PM
    Answerer