restlessheron19 said:I was messing around with this example...
HOW TO DELETE MULTIPLE ROWS (EMPTY CELLS) ALL AT ONCE.................
The macro command only wipes out rows inside the table... it doesn't touch the ones below it..
Why's that happening?
The tutorial title is literally...how to delete multiple rows (empty cells) all at once
Yeah, but within a specific data range.
If you want to figure out why, you gotta understand how Microsoft Office—and specifically Excel—actually works. Basic rule: if you want to do anything (like formatting) to some text or cells, you have to tell Excel exactly what you're targeting. You've got to select the text, the row, the column, or the cell first. Once the data is selected, then you can actually do stuff to it.
Same deal here: if you grab the first three rows and delete them, you’ll still end up with the same total number of rows because Excel just replaces the deleted ones with new ones. It'll always maintain that count of 1,048,576 rows (or whatever the limit is), and the same goes for columns.
That tutorial shows a few different ways to kill rows. The old-school way, using GoToSpecial, or running a Macro to clear out empty rows.
When we talk about deleting empty rows, we're talking about a specific range, a table, or a selection. Deleting every other row in the whole sheet would be insane.
1. So, we can target specific rows we want gone. We can select adjacent or non-adjacent rows and wipe them (whether they're empty or not) using the GoToSpecial command (sticking strictly to those specific rows).
2. Picture a table covering the range A2:A1000 filled with data. For whatever reason, there are gaps where some rows are totally blank. Instead of clicking through every single one manually—which sucks when there are tons of them—we can just use a Macro to tell Excel to nukes all empty rows within that specific range. The line of Macro code would look like this:
Code:
(deletes all blank cells in the range A2:A1000)
Range("A2:A1000").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
Range("A2:A1000").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
See, we don't even need to highlight anything because we explicitly told Excel the target is range A2:A1000. The macro will scan that area and kill every row where it doesn't find data. But, just like before, new rows will pop up underneath to make sure the sheet stays full. The row numbers stay the same, but the order of the table rows shifts up.
3. You could also use a macro to tell Excel to delete every empty row in Column D. That'll affect the rest of the rows too, since we're working with entire rows. Unless, of course, you're only trying to clear specific cells in that column.
Try this: set up two tables in Column A. Let's call them tbl1 (A1:A10) and tbl2 (A14:A20). Fill everything in. Now, delete the first five rows and watch what happens to tbl2 (it shifts up) and notice Excel still has its full set of rows ready to go.