When working with Excel data tables, you often find blank rows below the data, some containing some formatting like cell color or borders, but no actual data. The question is how are you selecting those blank rows and how are you deleting the rows (through all the columns horizontally)? Also, just imagine if you had hundreds of blank rows. We need a better solution than clicking and dragging to select hundreds of blank rows.
A simple solution is to use a keyboard short cut to select all the blank rows vertically down from a starting position and use the Delete Sheet Rows command to delete all the selected rows.
Scenario:
In our example (Figure 1), there are 10 valid data rows (Rows 4 through 14), and 5 rows at the bottom of the table that are essentially blank/without any data. Our objective is to keep Excel Sheet rows 1 through 14 and delete rows 15 through 19 that are blank.
Solution:
Use the CTRL + SHIFT + â keyboard shortcut to select the blank rows starting from Row 15, and the Delete Sheet Rows command to delete the rows entirely.
Steps:
- Click on the first blank Row Number (# 15 in this E.g.)
- Press CTRL + SHIFT + â (this selects all the blank rows 15 through 19)
** Note: If the short cut takes you all the way down to row million+ (1,048,526, figure 2), then you don’t have any blank rows, and you have reached the bottom row of the Worksheet. Press CTRL + HOME to return to Cell A1, the first cell at the top. - Click on the Delete drop down button on the Ribbon (Home tab)
- Click on Delete Sheet Rows option in the drop-down menu (Figure 1)
Alternately, after selecting all the sheet rows, you can Right-click on a Sheet Row number and choose Delete.
