none
Cut and paste column info from filter search into new sheet RRS feed

  • Question

  • Hi there, 

    I have tried to filter out a whole load of information via the filter (drop-down) in a particular column.  I click the option for "add this to search list" or whatever it is and then have a whole heap of financial info which I need - looks great.  However, when I try and cut and paste this selected filtered out info it just tries to cut and paste the ENTIRE sheet, rather than just the section which I (visibly!!) just filtered out? Meh?  I am confused.  Is there a way to select data you need to actually CUT and paste this into another sheet.  

    To explain, I need to be able to CUT the data I need out of the existing sheet I'm working on, so as that I am left with the remainder which is of importance.  The remainder of this information is important and likewise the stuff I am cutting out is important to be able to paste into a new sheet.  

    There's probably a really simple way of doing this, but if there is my mind hasn't caught up yet so please someone explain.  

    Help! 

    Thanks in advance for any useful assistance ;)  

    FP 

    Tuesday, May 26, 2015 3:23 PM

Answers

  • You can't actually cut the rows out of non contiguous rows. However, you can copy and paste the filtered data and delete the contents of the rows as follows.

    Apply the AutofFilter/s as required.

    Select the Filtered data.

    Click "Find & Select" (Far right of Home ribbon).

    Select "Go To Special" from drop down.

    Select "Visible cells only" and OK.

    Position cursor over the selected area and Copy.

    Select the first cell at the required destination and Paste.

    Go back to Filtered worksheet and while filtered data is still selected, press Delete and it will delete the data but not remove (cut) the rows.

    Depending on the data and how you have it set up then you might be able to Sort the data and then delete the empty rows. You can even consider adding a column with an initial index by inserting =ROW() and copy down for the length of the data and then Copy the column and paste special values to remove the formulas. Then sort on another column so that the empty rows are together for deleting and then re-sort on the index column to return to original sequence.


    Regards, OssieMac

    • Marked as answer by Frecklepaw Thursday, May 28, 2015 7:40 AM
    Wednesday, May 27, 2015 4:07 AM

All replies

  • You can't actually cut the rows out of non contiguous rows. However, you can copy and paste the filtered data and delete the contents of the rows as follows.

    Apply the AutofFilter/s as required.

    Select the Filtered data.

    Click "Find & Select" (Far right of Home ribbon).

    Select "Go To Special" from drop down.

    Select "Visible cells only" and OK.

    Position cursor over the selected area and Copy.

    Select the first cell at the required destination and Paste.

    Go back to Filtered worksheet and while filtered data is still selected, press Delete and it will delete the data but not remove (cut) the rows.

    Depending on the data and how you have it set up then you might be able to Sort the data and then delete the empty rows. You can even consider adding a column with an initial index by inserting =ROW() and copy down for the length of the data and then Copy the column and paste special values to remove the formulas. Then sort on another column so that the empty rows are together for deleting and then re-sort on the index column to return to original sequence.


    Regards, OssieMac

    • Marked as answer by Frecklepaw Thursday, May 28, 2015 7:40 AM
    Wednesday, May 27, 2015 4:07 AM
  • Thanks OssieMac, this worked a treat and got me out of a tough patch as I had a lot to do on the sheet.  Muchos gracias :) 
    Thursday, May 28, 2015 7:41 AM