Quickly delete unused blank rows or columns from selected range/active sheet/all sheets

This method will guide you to select all blank cells by Go To Special feature, and then delete empty rows or columns by Delete Sheet Rows feature or Delete Sheet Columns feature in Excel. Please do as follows:

1. Select the range where you want to condense all empty rows/columns/cells, and click Home > Find & Select > Go To Special. See screenshot:

2. In the opening Go To Special dialog box, please check the Blanks option, and click the OK button. See above screenshot:

Now only blank rows are selected in the specified range. See screenshot:

3. Click Home > Delete > Delete Sheet Rows. See screenshot:

So far all blank rows are removed and condensed from the specified range. See above screenshot:

For condensing and removing all empty columns, please click Home > Delete > Delete Sheet Columns after selecting all blank cells.

Note: Make sure there are only blank rows or blank columns in your selected range before applying the Delete Sheet Rows or the Delete Sheet Columns feature, if the range is mixed with blank rows and columns, some contents will be deleted at the same time.

Kutools for Excel supports the Delete Blank Rows utility to help Excel users to quickly delete all blank rows from a selected range, or delete all blank rows from active/selected/all worksheets in Excel.

1. Click Kutools > Delete > Delete Blank Rows, and then specify the deleting scope from the submenu. See screenshot:

Note: For deleting all blank rows from the specified range, please select the specified range before clicking Kutools > Delete > Delete Blank Rows > In Selected Range.

2. And then a dialog box pops out and shows how many blank rows were deleted successfully. Just click the OK button to close it.

Now all blank rows have been deleted and condensed from the specified scope, such as active sheet, all sheets, etc. See screenshot:

Kutools for Excel provides another Delete Hidden (Visible) Rows & Columns utility to delete all empty rows or columns from the selected range, or from active/selected/all worksheets in Excel easily.

1. Click Kutools > Delete > Delete Hidden (Visible) Rows & Columns.

Note: For deleting all empty rows or columns from the specified range, please select the specified range before clicking Kutools > Delete > Delete Hidden (Visible) Rows & Columns.

2. In the opening Delete Hidden (Visible) Rows & Columns dialog box, please do as follows:

(1) Specify the deleting scope from the Look in drop down list. In our case, we selected the In All Sheets;

(2) Check the Rows option or Columns option which you will delete in the Delete Type section;

(3) Check the Blank columns (or Blank rows) option in the Detailed type section;

(4) Click the Ok button.

3. And then a dialog box pops out and shows how many blank columns (or rows) have been deleted. Just click the OK button to close it.

So far all empty rows/columns have been deleted from the specified range or active/selected/all sheets based on specified deleting scope.

Clear table formatting style without losing table data in Excel