Delete hidden rows in filtred table with VBA

  • Hi,


    I have a table (Table1) with a lot of records. I want to filter, say all 1,2,3,4,5,6,7,8,9,10 in column A (This can be text as well).
    After that I want to delete all the rows that are hidden. Is there a better way to do that then I do with this?


    What I do is copy the filtred records to the right of Table1.
    Then delete all the records in Table1. After that paste the records back in Table1


    Any better way are most welcome...
    /Tony

  • Re: Delete hidden rows in filtred table with VBA


    Please read the Forum Rules before posting - you must include code tags when posting code - Rule 1. http://www.ozgrid.com/forum/announcement.php?f=8 I have added them for you this time.
    To increase the chances of getting a reliable solution please also upload a sample workbook with dummy data and all sensitive data removed.

    Regards
    [SIZE=3]Anthony
    [/SIZE]&WCF_AMPERSAND[SIZE=3]
    [/SIZE]&WCF_AMPERSAND&WCF_AMPERSAND&WCF_AMPERSAND[SIZE=2]You have your way. I have my way. As for the right way, the correct way, and the only way, it does not exist.[/SIZE]




  • Re: Delete hidden rows in filtred table with VBA


    This will delete all rows less than 10



    '

  • Re: Delete hidden rows in filtred table with VBA


    Hi,


    Thank you for the quick responce.
    But it seems like your code does not work.
    I will attache an example, so you see what I'm trying to do.


    forum.ozgrid.com/index.php?attachment/59315/


    This is the adapted code for the example.
    Remember that Table1 is about 26000 rows and a lot more entitys that are not within the filtred values.



    ' *********************
    ' * My way *
    ' *********************


    ' *********************
    ' * Your way (Adapted) *
    ' *********************

  • Re: Delete hidden rows in filtred table with VBA


    Do not delete the rows that are hidden but the rows that are not
    So filter the rows you want to delete instead of the opposite.


    Code
    columns(1).specialcells(12).entirerow.delete
  • Re: Delete hidden rows in filtred table with VBA


    Hi,
    I have seen this elswere on the web. But that can´t be used in this case.
    There are a lot of additional values to filter out. And they are not static..

Participate now!

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