+ Reply to Thread
Results 1 to 26 of 26

Remove Visible Rows from Filtered Table

  1. #1
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,910

    Remove Visible Rows from Filtered Table

    Everything I've read says that this code should work:
    Please Login or Register  to view this content.
    I get the Delete Method of Range Class Fails message on the indicated line.
    One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.

    A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.

  2. #2
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: Remove Visible Rows from Filtered Table

    Works for me...

    Edit...Perhaps a sample upload...
    Good Luck
    I don't presume to know what I am doing, however, just like you, I too started somewhere...
    One-day, One-problem at a time!!!
    If you feel I have helped, please click on the star to left of post [Add Reputation]
    Also....add a comment if you like!!!!
    And remember...Mark Thread as Solved.
    Excel Forum Rocks!!!

  3. #3
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,910

    Re: Remove Visible Rows from Filtered Table

    I'll have to desensitize the data. I cleared the duplicates manually and now I can't duplicate the problem myself - I'll have to wait until I import tomorrow's data.

  4. #4
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: Remove Visible Rows from Filtered Table

    A shot in the dark...Try this...
    Please Login or Register  to view this content.

  5. #5
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,113

    Re: Remove Visible Rows from Filtered Table

    @ sintek,

    Your post does not comply with Rule 115 sub paragraph "G" of our Forum RULES.

    Don't post questions regarding breaking or bypassing any security measure. Such posts will be deleted, and repeat offenders will be banned. This includes posting of links to sites that offer software, code or services to do this or posting of code to remove passwords. Clause B, Section "F" as amended in addendum E dated 25 November 2020, prohibits any Firearms or Shots on this Forum

    Tread Very Carefully, or Else!!!
    Please consider:

    Be polite. Thank those who have helped you. Then Click on the star icon in the lower left part of the contributor's post and add Reputation. Cleaning up when you're done. If you are satisfied with the help you have received, then Please do Mark your thread [SOLVED] .

  6. #6
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: Remove Visible Rows from Filtered Table

    @ Winon

    Raas jou koppie.......Kry 'n Doppie

  7. #7
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,910

    Re: Remove Visible Rows from Filtered Table

    I'll have to wait until I have a failed data set and try it. I can follow the logic in the code above except for:
    Please Login or Register  to view this content.
    What is the offset doing?

    Is this the same as going from last row to first row up column 10 and deleting line by line? I've done that, and it takes some time. I noticed that the manual: filter / delete / unfilter goes a lot faster.

  8. #8
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: Remove Visible Rows from Filtered Table

    Is this the same as going from last row to first row up column 10 and deleting line by line?
    Not looping...
    Selection of Filtered Visible rows from below header to and including last row

    Edit ...
    Has the same purpose as
    Please Login or Register  to view this content.
    Last edited by sintek; 01-25-2018 at 02:48 PM.

  9. #9
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: Remove Visible Rows from Filtered Table

    I had an issue with 2013 Office a while back where the Table had become corrupt...This was a workaround...
    Ended up deleting and recreating recently...

  10. #10
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,910

    Re: Remove Visible Rows from Filtered Table

    OK, I had to wait to get a download that failed, and I managed to desensitize the data. I am only interested in the RemoveDups macro. The other macro in the workbook works fine.
    Attached Files Attached Files

  11. #11
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: Remove Visible Rows from Filtered Table

    Code in Post4 works...
    Change in your code...
    Please Login or Register  to view this content.
    to
    Please Login or Register  to view this content.

  12. #12
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,910

    Re: Remove Visible Rows from Filtered Table

    I get Object Variable or With Block variable not set when I use that line.

    I'm still not clear what that line is supposed to accomplish. I am deleting a range shifted down one row from the filtered data. Doesn't that leave one row of data on the top and try to delete row below the filtered range? What if that row beyond the filtered range has data I want to keep or is outside the table entirely?

  13. #13
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,910

    Re: Remove Visible Rows from Filtered Table

    I copied the code in post 4 verbatum and I will not argue with apparent success.

    As I mentioned above, I can follow all of it except the one line discussed above.

    Also I am curious as to why the "standard" way of doing this doesn't work.

  14. #14
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: Remove Visible Rows from Filtered Table

    You are wanting to delete all rows containing "TRUE"
    What code does is delete all except header...i.e Offset(1,0)

  15. #15
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Remove Visible Rows from Filtered Table

    Since the delete is applied to visible rows only, you might use
    Please Login or Register  to view this content.
    Don
    Please remember to mark your thread 'Solved' when appropriate.

  16. #16
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,910

    Re: Remove Visible Rows from Filtered Table

    sintek, I kind of thought it was something along those lines. It's just that Range("Table_Data") is the same thing as Range("Table_Data[#Data]") which excludes the header. However, I am not sure that "translates" when you "convert" it to a list object. Thank you for clarifying that.

    Also, I would like to drop the word, "apparent" from my previous post. I just finished the QA on my file and the results are identical with what I got with the manual process.

  17. #17
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: Remove Visible Rows from Filtered Table

    xlnitwit just solved your own code issue
    Thanks for rep +

  18. #18
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Remove Visible Rows from Filtered Table

    Tables are curious beasts at the best of times, and especially so when filtered. For example, this will work if the table is not filtered
    Please Login or Register  to view this content.
    but will do nothing- with no error of any kind- if the same table has a filter in effect.

  19. #19
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: Remove Visible Rows from Filtered Table

    @ xlniwit
    Strange that this does not work for dflak in his code from Post 1
    Please Login or Register  to view this content.

  20. #20
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,910

    Re: Remove Visible Rows from Filtered Table

    xlinitwit: I want to make sure I understand the code you presented about these "curious beasts" called tables. I live and die by tables .

    Are what you are telling me that if the filtered data are in a table, then you don't have to use special cells when deleting them? This would be big news, indeed!

  21. #21
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Remove Visible Rows from Filtered Table

    @Sintek,

    Not that strange. You are referring to the Autofilter property of the Worksheet, not the ListObject. The two are only the same if the active cell is inside the listobject when the code runs.

  22. #22
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Remove Visible Rows from Filtered Table

    Quote Originally Posted by dflak View Post
    xlinitwit: I want to make sure I understand the code you presented about these "curious beasts" called tables. I live and die by tables .

    Are what you are telling me that if the filtered data are in a table, then you don't have to use special cells when deleting them? This would be big news, indeed!
    They don't have to be in a Table- the same is true for a regular range that is autofiltered.

    If you use tables a lot, you should be very careful about deleting Listrows though!

  23. #23
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: Remove Visible Rows from Filtered Table

    So I guess simply...
    Please Login or Register  to view this content.

  24. #24
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,910

    Re: Remove Visible Rows from Filtered Table

    Now, that makes sense!

    I have to play with that code a bit more; it may help me with another process whose efficiency I'm not totally pleased with.

  25. #25
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: Remove Visible Rows from Filtered Table

    @ xlnitwit
    only the same if the active cell is inside the listobject when the code runs.
    Actually had to test above to understand..wow...very finicky. Hence the error in Post 12

  26. #26
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Remove Visible Rows from Filtered Table

    I assume it was a deliberate choice given that a worksheet could only have one autofilter applied before Tables came along.

    Oh- thanks for the rep!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Selecting the first x visible rows of a filtered table
    By DreamEyes in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 09-27-2017, 11:05 AM
  2. [SOLVED] Format Filtered Visible Rows of Table
    By Redled89 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-27-2017, 12:40 AM
  3. [SOLVED] Count of visible rows in a filtered range
    By brucemc777 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-16-2014, 01:17 PM
  4. Pivot table to refresh and source data to change using visible rows on a filtered sheet
    By papasmurfuo9 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-27-2014, 11:32 AM
  5. Need some help on dynamic dropdow for filtered row or for visible rows only
    By dvpnp in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 12-05-2013, 12:21 PM
  6. Use visible rows from autofilter to build Pivot table. Or use visible rows to Copy/Paste
    By mwhitedesigns in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-08-2012, 10:34 AM
  7. Formula in visible rows of filtered column
    By [email protected] in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-24-2009, 01:36 PM

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