Iteratively delete rows from a spreadsheet

**wruwtrix** · 04-13-2009, 09:40 PM

I have a very large spreadsheet (about 50,000 rows, and to CM of columns) with blocks of data 20 rows high (rows 1-20 are from Building A, rows 21-40 are from Building B, etc). However, not every row in each block of 20 has information in it - some are just placeholders. For example, some blocks may have rows 1-18 filled with data while other blocks may have only 1-6 filled with data.

I am interested in programming a macro that would delete the placeholder rows out of the spreadsheet based on a certain criteria. This would probably halve the size of my spreadsheet.

In faux-code:

For row i
i = 1 to 50,000
If Column B = #NA
Delete row i

Is this something that can be done with a macro, or do I need to go through all of these rows by hand? Obviously, I haven't programmed macros before (I've taken code and run it), but I've programmed in other languages (Java, C#). Could someone point me in the right direction?

Thanks,

V. Hagberg

**JBeaucaire** · 04-13-2009, 10:45 PM

Your syntax is pretty dead on...except when deleting rows you really should start at the bottom and work up.

Please Login or Register  to view this content.

Give that a try. 50,000 rows may take a minute or so...

**wruwtrix** · 04-14-2009, 03:25 PM

I tried it out and this worked great. It did take a while (maybe 20 minutes), but that's certainly less time than me doing it by hand.

Thanks so much for your speedy reply,

V. Hagberg