I was using the code below to delete rows not equal to a cell value that was defined in the main code that calls this one.
When using it against a normal data range it works great but when using it on a data table, it hangs atAny help would be much appreciated.
Please Login or Register to view this content.
Please Login or Register to view this content.
Last edited by xtroublex; 11-07-2019 at 05:46 PM. Reason: Solved
See the yellow banner at the top of this page …
Apologies. I have done as requested.
A code is even not necessary just using Excel basics like a filter then delete in less than 5 seconds ! …
And if a code is really necessary, activate the Macro Recorder and operate the same operations
then you will get your own code base.
For small to middle data I would use the same way within a code,
for big data I may use a helper column, a sort then deleting all at once …
This is one macro in a longer automated process. It is designed to be completely hands off. I have tried a normal way of just Deleting rows that are <> C5 but it takes forever. This code was a great solution until I changed the data range to a table using a query to import the data. Why would it not work with a data table as opposed to a range?
The number of records in the real table is 47k
As your start to loop instead of a filter a beginner starter :
PHP Code:Sub Macro1()
Dim S$, R&
S = Sheet7.[C5].Value2
Application.ScreenUpdating = False
With Sheet5.ListObjects(1).ListRows
For R = .Count To 1 Step -1
If .Item(R).Range(1).Value2 <> S Then .Item(R).Delete
Next
End With
Application.ScreenUpdating = True
End Sub
Do you like it ? So thanks to click on bottom left star icon « ★ Add Reputation » !
Last edited by Marc L; 11-07-2019 at 04:40 PM. Reason: optimizing the Macro Recorder code …
Thank you for that. However it doesn't have the speed that the original code (yes, the one that doesn't work now. lol) had. I will be running this on up to 20 files and it takes 3 minutes per file. That's with Calcs Screen Updating etc all switched off. This is my dilema. I cannot find a solution so far that will run as quickly as the original code. Does it make sense that the original code works with a regular data range but not a table?
I already saw children operating manually in less than a minute just using Excel basics (see 'big data' in post #4)
while any bad looping code was lasting much more than 5 minutes (70K to 90K rows) …
So the first time I changed my mind and think about trying to apply the main TEBV rule.
Let me clarify,
3min was only running the code you provided. I do not know what TEBV is, and I'm assuming you do not know why the code I originally posted does not work with a table only because of a lack of an answer to that question.
Thanks
Hey xtroublex,
I have found when running a passer to delete multiple rows, it's a lot faster to create a range in memory by using .Rows, then delete the range, maybe that would work better for you?
That looks something like this--
Please Login or Register to view this content.
Last edited by 1aaaaaaaaaaaaaaa; 11-07-2019 at 05:02 PM.
Think Excel Before VBA ‼
An alternative to the child way (yet demonstrated in several threads within this forum) is an advanced filter (Excel Basics again)
in order to copy only the desired rows to another place (for example in column AH) then deleting all previous columns at once
(which is so far faster than deleting row by row) then recreating the table if necessary …
Try replacing
rng.EntireRow.Delete
with
rng.Delete shift:=xlUp
No, it hangs at the new line rng.Delete shift:=xlUpOriginally Posted by Mrrrr
As you can obviously keep that code just starting by canceling the table (back as a standard range, see UnList in VBA help)
and at the end of the procedure recreating the table if necessary … So you already have 3 paths as all paths lead to Roma !
Weird that it doesn't hang for me on your Example workbook...
So for you it hangs even in that workbook?
Hangs at rng.deleteHey xtroublex,
I have found when running a passer to delete multiple rows, it's a lot faster to create a range in memory by using .Rows, then delete the range, maybe that would work better for you?
That looks something like this--
Please Login or Register to view this content.
Altering ListObject is going to be slower than altering standard range. As it has additional layer.
But try following and see if it's any faster.
Please Login or Register to view this content.
?Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something.?
― Robert A. Heinlein
How many rows are we talking about? It could be your dataset could be managed better in a true database.
With a database you could run a simple query "DELETE FROM table WHERE x = true"
I'm sensing that you may be running into memory limitation issues, if you're trying to modify too many rows, if as you say it's hanging (not just throwing an error)
...and not memory limitation of your computer, but the Excel application engine
Last edited by 1aaaaaaaaaaaaaaa; 11-07-2019 at 05:45 PM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks