LinkBack URL
About LinkBacks
I have the below which deletes any row that does NOT contain the string "TypeA Disease|English Irish American Matches|". Is there any way to speed the below up as it takes about 2 minutes to run.
Please Login or Register to view this content.
I have the below which deletes any row that does NOT contain the string "TypeA Disease|English Irish American Matches|". Is there any way to speed the below up as it takes about 2 minutes to run.
Please Login or Register to view this content.
Last edited by ScabbyDog; 01-03-2015 at 09:19 AM.
try this:
Set Rng = Range("C1:C" & Cells(Rows.Count, 3).End(xlUp).Row)
This minimizes the RANGE to Search and hence increase the speed proportionally to the size of the array.
success
Hi,
Always avoid VBA loops wherever possible. They are just too slow when compared to standard Excel functionality
The fastest way I know of doing this sort of stuff is to use Autofilter to show the rows you want to delete then just select and delete them in one hit.
Use a helper column (say column D) to identify the rows, e.g.
Formula:
Please Login or Register to view this content.
Then
' change the 10000 as necessaryPlease Login or Register to view this content.
Richard Buttrey
RIP - d. 06/10/2022
If any of the responses have helped then please consider rating them by clicking the small star iconbelow the post.
Getting a 'delete method of range class failed' for the very last line of code Richard. I think it's because it can't execute the code while a filter is applied? Maybe I am wrong.
Please Login or Register to view this content.
No, deleting filtered rows is a quite normal practice, and indeed the code worked for me when I tested it.
Have you perhaps got sheet protection on.
If not upload the workbook so that we can see what might be the problem.
Attached....in next post...
Last edited by ScabbyDog; 01-03-2015 at 10:50 AM.
sampelbook (1).xlsm
use this one. Thanks!
Hi,
Seemingly Excel Tables don't like the syntax. Personally I rarely use the Table functionality preferring to use normal ranges. I find it just complicates things when you need to reference cells in the table from outside it.
You could either convert your Table to a Range and use the code I gave you or change the code syntax to
Please Login or Register to view this content.
Brilliant Richard. Hero.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks