Everything I've read says that this code should work:
I get the Delete Method of Range Class Fails message on the indicated line.Please Login or Register to view this content.
Everything I've read says that this code should work:
I get the Delete Method of Range Class Fails message on the indicated line.Please Login or Register to view this content.
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.
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!!!
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.
A shot in the dark...Try this...
Please Login or Register to view this content.
@ 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] .
@ Winon
Raas jou koppie.......Kry 'n Doppie
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:
What is the offset doing?Please Login or Register to view this content.
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.
Not looping...Is this the same as going from last row to first row up column 10 and deleting line by line?
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.
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...
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.
Code in Post4 works...
Change in your code...
toPlease Login or Register to view this content.
Please Login or Register to view this content.
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?
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.
You are wanting to delete all rows containing "TRUE"
What code does is delete all except header...i.e Offset(1,0)
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.
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.
xlnitwit just solved your own code issue
Thanks for rep +
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
but will do nothing- with no error of any kind- if the same table has a filter in effect.Please Login or Register to view this content.
@ xlniwit
Strange that this does not work for dflak in his code from Post 1
Please Login or Register to view this content.
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!
@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.
So I guess simply...
Please Login or Register to view this content.
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.
@ xlnitwit
Actually had to test above to understand..wow...very finicky. Hence the error in Post 12only the same if the active cell is inside the listobject when the code runs.
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!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks