Mass deleting excel rows based on criteria

July 22, 2022 11:06 AM

I have a spreadsheet with 30k rows. I want to delete any row that isn't blank in column G. I'm using Excel 365 on a Mac. How?
posted by BuddhaInABucket to Computers & Internet (8 answers total)

Response by poster: Mods, please delete- I didn't even think about the super obvious solution of holding shift and using the scroll bar.
posted by BuddhaInABucket at 11:09 AM on July 22, 2022 [1 favorite]

https://excelformulabot.com/


Just in case someone sees this and wants to try this AI bot.
posted by JohnnyGunn at 11:26 AM on July 22, 2022 [6 favorites]

I would sort by column G and then select and delete. The reason I would sort and not filter is that Excel gets bogged down quite easily when more than 9,999 different rows are deleted at once. If the rows are next to each other and not mixed in with rows that you're keeping, they count as a single selection and are much less likely to slow things down.
posted by soelo at 11:30 AM on July 22, 2022 [22 favorites]

If you're going soelo's recommended route (which is also the way I'd go), you might want to add a temporary column drag-filled with ascending integers first, so that you can sort by that after doing the column G deletions to put your rows back into the same order they started in.
posted by flabdablet at 11:33 AM on July 22, 2022 [14 favorites]

After sorting as described above, if the number of rows you're keeping is less than the number of rows you'd be deleting, you can just copy those into a new sheet and delete the original. There are various paste special options to allow you to preserve original column width, values only rather than formulae if that's desirable, etc.
posted by Athanassiel at 2:08 PM on July 22, 2022 [1 favorite]

Oh please for the love of all that is holy keep the original and do this deleting on a copy.

Please. I beg you.
posted by bilabial at 3:25 PM on July 22, 2022 [13 favorites]

Another way to do it is to filter out rows with filled cells, select the entire thing, then ALT + ; to select only displayed.

Ctrl + C to copy, then Paste Special (select whichever one makes the most sense) into a new sheet.

It should paste (and preserve order) without any empty rows (the filtered out ones).
posted by porpoise at 6:44 PM on July 22, 2022 [2 favorites]

I love ASAP Utilities for this type of thing.
posted by I_Love_Bananas at 5:04 AM on July 23, 2022 [1 favorite]