+ Reply to Thread
Results 1 to 15 of 15

Delete Rows After a filter

  1. #1
    Forum Contributor
    Join Date
    11-22-2006
    Posts
    100

    Delete Rows After a filter

    Hello,

    I used to work a lot on VBA but now I forgot almost everything and I would need your help to restart again my work on VBA. I have a worksheet with data from Column A to AA and I have a filter on Row 1. I would like to select from this filter a certain value for column D and then delete the rows I dont need. So my code starts like this:

    Please Login or Register  to view this content.
    But after that I would like to delete the rows that are matching the criteria <> MY. Do I need to select manually the rows and then after make Selection.delete or does it exist a code that can directly delete the rows that I have selected (<> MY) ?

    Thank you for your help !!!

    I hope this time my message is correct !

  2. #2
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    28,468
    Hi
    You very rarely need to select objects. You can simply refer to them directly. For instance in this case you'd probably refer to the filtered rows with the SpecialCells(xlCellTypeVisible) instruction and use .EntireRow.Delete

    HTH

  3. #3
    Forum Contributor
    Join Date
    11-22-2006
    Posts
    100
    Thanks Richard and as well Yeaster (in my last post) ! I think I am really close but actually I need only more tip: What do I need to put before SpecialCells(xlCellTypeVisible) ? because I am receiving Sub or function not defined.

    Thanks again for your help for this very basic question ....

  4. #4
    Valued Forum Contributor Shijesh Kumar's Avatar
    Join Date
    05-26-2008
    Location
    Bangalore / India
    MS-Off Ver
    2000
    Posts
    717
    try this code



    Please Login or Register  to view this content.
    Last edited by Shijesh Kumar; 11-24-2008 at 08:33 PM.

  5. #5
    Forum Contributor
    Join Date
    11-22-2006
    Posts
    100
    Sorry I can t find it again: it says "cannot use that command on overlapping selection " Maybe is it because my filter is in row 1 ?

    Thanks !

  6. #6
    Valued Forum Contributor Shijesh Kumar's Avatar
    Join Date
    05-26-2008
    Location
    Bangalore / India
    MS-Off Ver
    2000
    Posts
    717
    On the White VBA window delete everything..
    and then copy paste my the above code....

    or


    can u upload ur workbook..
    Last edited by Shijesh Kumar; 11-24-2008 at 08:34 PM.

  7. #7
    Forum Contributor
    Join Date
    11-22-2006
    Posts
    100
    Sorry but I still have this message appearing with yellow background on the last line

    Please Login or Register  to view this content.

  8. #8
    Valued Forum Contributor Shijesh Kumar's Avatar
    Join Date
    05-26-2008
    Location
    Bangalore / India
    MS-Off Ver
    2000
    Posts
    717
    can you post your sample excel sheet

  9. #9
    Forum Contributor
    Join Date
    11-22-2006
    Posts
    100
    Yes, here is attached ! Hope this can help ! I have deleted some columns for data privacy but actually there were more columns and some were hidden (myabe that can be the reason ?? )

    Thanks
    Attached Files Attached Files

  10. #10
    Forum Contributor
    Join Date
    11-22-2006
    Posts
    100

    Thumbs up Closed !

    Your code is working on the small file ! It must come from my hidden columns on my original file ! Well, I will try to fix this !!

    Thanks a lot all for your help !!!

  11. #11
    Valued Forum Contributor Shijesh Kumar's Avatar
    Join Date
    05-26-2008
    Location
    Bangalore / India
    MS-Off Ver
    2000
    Posts
    717
    I hav put the code in the attached and hav replaced some of th "MY" with dummy data..


    First see the data and then run..

    it will work...

    Change the code as per you need.
    Attached Files Attached Files

  12. #12
    Forum Contributor
    Join Date
    11-22-2006
    Posts
    100
    Bump no response

  13. #13
    Valued Forum Contributor Shijesh Kumar's Avatar
    Join Date
    05-26-2008
    Location
    Bangalore / India
    MS-Off Ver
    2000
    Posts
    717
    Is the code still not working..

  14. #14
    Forum Contributor
    Join Date
    11-22-2006
    Posts
    100
    Working perfectly !! Thanks a lot mate !

  15. #15
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    28,468
    Hi,

    Assuming your table of records started in A10 with row 10 being the column headers, something like:

    Please Login or Register  to view this content.
    HTH

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1