4 Ways to Delete Blank Cells in Excel: Find, Filter, VBA

4 ways to Delete Blank Cells in Excel: Find and Select, Filter, VBA

---

When working with large datasets in Excel, blank cells can often disrupt the workflow and make the data look disorganized. While Excel offers various methods to remove these blank cells, knowing which method to use can save both time and effort. This guide will take you through four comprehensive methods to get rid of those pesky blank cells.

---

1. Manual Deletion

• Select Cells: Click and drag to select the cells or range where blank cells appear.

• Context Menu: Right-click on your selection.
• Delete Option: Choose the Delete option from the context menu.

• Or Use shortcut: Ctrl ‘-‘
• Shift Cells: A dialog box will appear giving you options to either shift cells left or up, or to delete the entire row or column. Choose your preference and confirm.

Pros and Cons

• Pros: Simple, straightforward, and no special skills needed.
• Cons: Time-consuming for large datasets.

2. Using Find and Select

• Home Tab: Navigate to the Home tab on the toolbar.
• Find & Select: Located in the Editing group, click on Find & Select.

• Go To Special: Choose Go To Special from the dropdown.

• Blanks Option: In the dialog box, tick the Blanks option and click OK.

• Right-Click to Delete: All blank cells within the selection will be highlighted. Right-click on any cell in the selection and choose Delete.
• Use shortcuts: Ctrl + G > select special

Pros and Cons

• Pros: Efficient for deleting all blank cells within a specific range.
• Cons: Can accidentally delete required spaces if not careful.

3. Using Filters

• Column Selection: Click the column header to select the entire column.
• Data Tab: Navigate to the Data tab.

• Filter Option: Click the Filter button.

• Unselect All: In the drop-down, uncheck Select All and then check Blanks.

• Confirm: Click OK to filter out the blank cells.

• Delete: Select the blank cells and right-click to choose Delete.
• Use shortcut: Click the column header to select the entire column. Ctrl + Shift + L

4. Using VBA

• Developer Tab: Navigate to the Developer tab (activate it if it’s not visible) on the Excel ribbon. If it’s not visible, you’ll need to activate it. You can do so by right-clicking on the Excel toolbar and then selecting “Customize the Ribbon.” Check the “Developer” box and click “OK.”

• Visual Basic: Click on Visual Basic.
• New Module: In the VBA editor window, right-click on your workbook name in the “Project-VBAProject” pane (usually found in the upper left corner).

Then, select “Insert” and click on “Module.”

This will create a new module where you can paste your VBA code.

VBA

Sub DeleteBlankRows()
Dim rng As Range
Set rng = Selection
rng.SpecialCells(xlCellTypeBlanks).Delete Shift:=xlUp
End Sub

• Run Script: After pasting the VBA code into the new module, close the VBA editor. Go back to Excel and run the script by either pressing F5 while your cursor is inside the subroutine, or by going back to the Developer tab and clicking “Run Macro” to select and run your script.

Result!

---

Removing blank cells in Excel can streamline your data manipulation tasks and make your spreadsheets more organized. While each method has its pros and cons, the choice largely depends on the size of the dataset and your familiarity with Excel features.