Deleting multiple listrows

[storm8]

Hello,
is it possible to delete at once more than 1 row with listobject?
method listobjects(x).listrows.delete is for some reason incredibly slow to have it in loop and because of other tables on worksheet I cannot delete entire sheet row by entirerow.delete

generally, is it possible to work with multiple listrows in listobject?

any suggestions please? Thanks alot

 

[Jerry Sullivan]

A few months ago I was trying to help someone remove 2500 or so rows meeting a criteria that were scattered through a large data set.

It was taking a few seconds on my test data mockup but 30 minutes or more for the other person.

It took 10 posts or so to figure out that their data was in a listobject (Table),
and as you are finding, it can be painfully slow to delete each row.

We compared several different approaches and found the fastest result from:
1. Adding a column with the header "Delete" and the row number in each cell.
2. For each row to be deleted, replace the row number in the added column with the word "Delete"
3. Applying RemoveDuplicates using the added Column as the criteria
4. Clean up by deleting the added Column and Blank rows that will now be a continguous range
at the bottom of the table. You can delete those rows in one step manually or with your macro.

 

[storm8]

This seems as brilliant idea, thank you!
I also figured that since I usually want to delete end of table (eg last x rows) I might also clear contents from them and resize the table to match actual last row.

 

[Jerry Sullivan]

If you have any trouble getting the code to work, just post the code you have so far and I'll be glad to help.