How to delete multiple contiguous (and/or) non-contiguous rows, in an excel table

Maybe something like this:

Please Login or Register  to view this content.

Remember to test on a copy of the actual data.

abousetta

Hi Abousetta. Thanks. I actually have a solution and want to post it because I thought it might be useful to others on the forum. I couldnt find any other solutions and I think my solution is pretty good.

Please Login or Register  to view this content.

If any cell (or multiple cells) in a row is selected, either contiguously or non-contiguously, running this code will delete the row, even if it is a table. Non visible cells will not be deleted (ie. rows that are filtered in a table). Also, this method is very fast, much faster than some of the other solutions I came across. Kinly check it out and let me know what you think. Thanks Abousetta

Hello Hilton. The above code is most useful if you have a table and wish to choose various entire rows to delete at one time. I use it in a form. For your case you wish to delete only one row.

Please Login or Register  to view this content.

This is your key. Define the range (ie. the row containing your vehicle number), and use the "entire row delete" to delete the whole row.

An advice concerning your project.. In the solutions you see on the Forum, look carefully at the code to find the little code snippits that do something useful (that you need to do also). Improvise those techniques into a solution to your problem. I rarely get the exact answer I need (other people don't do my work), but I almost always get that little bit of code thats syntaxed in a way I've never seen before (the correct syntax), and I improvise that code to get the exact solution I'm looking for. Just a small advice. Good luck

Hilton1982,

Unfortunately you need to post your question in a new thread, it's against the forum rules to post a question in the thread of another user. If you create your own thread, any advice will be tailored to your situation so you should include a description of what you've done and are trying to do. Also, if you feel that this thread is particularly relevant to what you are trying to do, you can surely include a link to it in your new thread.

@jimmalk
didn't abousetta's code work? seems a lot simpler than yours and more efficient as there's no looping or preserving of arrays

Hi Joseph. I did the loop to find out how many rows are selected so I could show that number in a confirmation dialog box. Also the array is used to filter only unique row numbers (in case the user selected more than one cell in any row). At the end of the Sub, by deleting areas (instead of all the rows at once), that allows deleting non-contiguous rows of a table. An "all at once" delete fails. This code will delete rows even on filtered protected tables, with non-contiguos row selections. But.. I havent finished fully testing this because on a couple of occasions I had it fail when I deleted multiple non-contiguous rows near the end of a filtered table (but unfiltered always works great). I'm still trying to figure out if I'm missing something with this code, or if the fail has to do with other things I'm also doing when I do the row deletes (I intersect the active cell and row to do some highlighting and color changes on the table). I imagine there could be a better way to delete multiple non-contiguous row selections from a filtered table, but this is the best I could come up with (I still have a lot to learn)