Ive got the code below doing what i want, which is to delete an entire row if 'Unsettled' is found anywhere in a particular column.
Is there a more efficient way to do this when the number of rows is large?
Please Login or Register to view this content.
Last edited by intothewild; 11-24-2012 at 02:18 PM.
Give this a try
Please Login or Register to view this content.
Thanks,
Mike
If you are satisfied with the solution(s) provided, please mark your thread as Solved.
Select Thread Tools-> Mark thread as Solved.
Thats where i was headed, thanks Mike.
However, what i was asking was not this, but... is there a more efficient way to perform this task than going down the column cell by cell.
For example, in vba, would filtering and deleting the visible cells be quicker?
Or is there a completely different approach that is more efficient on resources/time to run.
The reason i ask is that i may have upto 30000 rows to process and may be performing the routine multiple times daily.
Last edited by intothewild; 11-24-2012 at 02:47 PM.
The solution I gave would be way faster then what you have. Yes you can delete filtered rows, but I believe you would need to sort that column first then filter the data and delete. Specialcells(xlCellTypeVisible) is funny when dealing with very large amount of rows.
true, forgot 'special cells visible' can be funny at times, thanks.
the question remains as to whether your improved method (cheers for that!) is 'optimal'?
this part is a small part of a much larger process which is taking too long so need to optimise all parts so the sum is faster.
Last edited by intothewild; 11-24-2012 at 03:03 PM.
ok, for test purposes (time to run)....im going to use this...
Would be good to get more methods to test though.Please Login or Register to view this content.
Last edited by intothewild; 11-25-2012 at 07:18 AM.
Mike,
Thanks for bringing up the issue of filter!
I know many people in this forum prefer filter to loop and they have their reasons and justifications.
One of the main advantages claimed by filter users is its speed.
Back in 2007, when MS came up with a table, I heavily used tables at work to store large data. I had problems with sorting data. As you said, it sometimes behaved in a funny way. At the time, I was not sure what the problem was. It was only when I joined this forum, someone sent me link to the problem with filtering and sorting in table excel 2007.
I have not learned the syntax for filtering; partly my mind is still haunted by the experience of table 2007. I am sure filtering is great, but I personally feel my data and formulas would be over the place after the filter, so will stick with a loop until I overcome my phobia with it.
Hi
Another way for deleting the rows
Please Login or Register to view this content.Please Login or Register to view this content.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks