Deleting filtered row from table after copying to another sheet

  • Hello all
    I have a table starting from Column B to G and it has date (for validation purpose) in column D.
    What i am trying to achieve is move data of rows whose date is past today to another sheet.
    i want to delete only table rows not the data in column A as it contains Serial No.
    i am able to do copy and paste or delete the rows but it delete's the complete row thereby upsetting the sequence number.
    please help me with the code.


    Thanks n regards

  • Hello,


    Not sure to understand your numbering formula in Column A ...


    Could =ROW()-4 be of any help ...?

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

  • Hello Carim
    =SUBTOTAL(3,$D$5:D7)
    I am trying this formula so that when any row is deleted the serial number autonumber adjusts. for example if i have 10 rows of data from A5 to A15 and row 7, 9 and 10 are deleted for outdateddata the serial number automatically changes from 1 to 12, not 1 2 3 4 5 6 8 11 12 13 14 15, i.e. missing the deleted rows.
    and moreover if you run the code on Sheet1(BookingPage) it gives an error.
    I am trying to delete the outdated data from Sheet1 and paste it to Sheet2(Oldrecord).


    Thanks in advance

  • Hello,


    Have you tried to replace your subtotal formula ... by


    Code
    =Row()-4

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

  • Hi again,


    The main question is to determine if you need a UNIQUE identifier Number... or if you need to have a simple counter ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

  • If we forget your counter formula ...


    Attach is your test file to copy filtered rows from Source to Destination... and then delete filtered rows from Source sheet...


    Hope this will help

  • Glad it solves your problem :wink:


    Thanks for your Thanks ...AND for the Like ... :smile:

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

  • Hello Carim
    Just got stuck again, if somehow all row data is deleted Row 5 onwards (i.e. from where Row()-4 formula started then have to rewrite the code. is there any way that instead of writing the code in cell it is done through VBA.

  • Hello,


    You can amend your macro and insert following instructions at the end ...



    Hope this will help

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

  • Hello Carim
    The problem now i came across is,
    1. if all the rows on sheet 1 have outdated data, the code delete's all the rows minus one and when i click again on delete row button it delete's it, but there is error "Delete method of class fail".
    I may not have copied the formula correctly (please correct me).


    Please check the attachment


    Thanks again

  • Hello,


    Thanks for your test file ...


    Will look into this issue of deleting ALL records

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

  • Once you have tested the macro ... feel free to share your comments ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

  • Hello Carim
    Thanks a ton, as of now it is working flawless. incase any problem will get back to you.


    Thanks again, keep doing the good work
    Regards


    Ajit

  • Good to hear it is all sorted out ... :wink:


    Thanks for your Thanks ..AND for the Like ...:smile:

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!