9 Quick Tricks to Delete Row If Cell is Blank in Excel

The cell that doesn’t contain a least of words or values is called a blank cell. Hence, if there are multiple blank cells that don’t have any value are labeled as blank lines in Excel. Now think about a data sheet that has inappropriate or blank values in the Excel sheet. In this case, we have to delete the row in Excel if any cell is blank. There are several methods to delete a row if any cell is blank in Excel, cells, or rows in Excel. In this article, we are going to demonstrate how to remove blank lines in Excel.


📁 Download Excel File

To practice please download the Excel file from here:


Learn to Delete Row If Cell is Blank in Excel with These 9 approaches

Here in this article to remove blank lines in Excel, we considered a dataset named Greatest Performers of 2022 containing First Name, Last Name, Category, and Age. There are 5 columns as well as 14 rows in the dataset. In this article, to delete a row if any cell is blank in Excel, we will use the keyboard shortcut, COUNTA function, Find and Replace Feature, Sort feature, etc. In addition, we are going to discuss the use of VBA Macro. To run VBA Macro save the workbook as .xlsm extension.

dataset to delete a row if any cell is blank in Excel


Method

1. Selecting Blank Cells Manually

The simplest way to remove blank lines is to select the cells and Right-Click on the mouse to get a Context Menu and delete simply. Please follow the procedure below.

 ⬇️⬇️ STEPS ⬇️⬇️

  • Initially, click on cells C6, D10, and E13 to select.

Blank lines to delete a row if any cell is blank in Excel

  • Then Right-Click on the Mouse to get a Context Menu.
  • Next, from the Context Menu, click on Delete from the list.

Remove blank lines using the context menu.

  • Thus a Delete box appears.
  • After that, select Entire Rows and click on the OK button.

Delete box to remove rows.

  • Therefore, rows containing blank cells are erased.

Blank lines removed.

Method

2. One-Click Action to Remove Lines with Blank Cells in Excel

By selecting cells and with the help of the keyboard key, one can easily erase any rows he wants. Please check the steps.

 ⬇️⬇️ STEPS ⬇️⬇️

  • Firstly, click on cells C6, D10, and E13 to select.
  • Secondly, Press Ctrl + -(Minus) from the Keyboard.
  • Thus a Delete box appears.
  • Thirdly, select Entire Rows and click on the OK button.

one click action using the keyboard to delete a row if any cell is blank in Excel

  • Therefore, by removing 3 rows containing blank cells, we get the final output.

blank rows removed.

Method

3. Using Keyboard Shortcut

To operate from Top Ribbon, the keyboard shortcut can be another key to delete rows containing blank cells. Please follow the procedure below.

 ⬇️⬇️ STEPS ⬇️⬇️

  • Primarily, click on cells C6, D10, and E13 to select.
  • Then press Alt to get the keys.
  • Next, press H for the Home tab.
  • After that, press D for the Delete feature.
  • Further press R to select the Delete Sheet Rows option.

Using the keyboard shortcut to delete a row if any cell is blank in Excel

  • Lastly, selected cells and rows remove, and get the final output.

Blank lines eliminated in Excel

Method

4. Using Go To Special Feature

Suppose you are asked to find out blank data and erase those rows to make the workbook concise. Use of the Go To Special option from the Find and Select feature from the Top Ribbon can be a key. Please follow the necessary steps.

 ⬇️⬇️ STEPS ⬇️⬇️

  • First, select the entire table.
  • Secondly, Click on Editing from the Home tab.
  • Then, click on Find and Select feature.
  • After that, select Go To Special option from the list.

Use of the Go To Special option to delete a row if any cell is blank in Excel

  • Therefore, the Go To Special Box appears.
  • Then, Select Blanks from the box and click on the OK button.

Go To Special box to select only blanks.

Blank cells highlighted.

  • Again, Right-Click on the Mouse to get a Context Menu.
  • Next, from the Context Menu, click on Delete from the list.

Context menu to delete rows.

  • Thus a Delete box appears.
  • After that, select Entire Rows and click on the OK button.

Delete box.

  • Therefore, rows containing blank cells are removed.

3 lines were erased.

Method

5. Using Find Command

Finding the blank rows you can delete rows containing blank lines easily. Please check out the steps.

 ⬇️⬇️ STEPS ⬇️⬇️

  • First, select the entire table.
  • Secondly, Click on Editing from the Home tab.
  • Then, click on Find and Select feature.
  • After that, select the Find option from the list.

Find option to find out to the rows if any cell is blank in Excel

  • Meanwhile, A box named Find and Replace appears.
  • In addition, Keep blank in Find what option.
  • Then, Select Search by rows from the drop-down box.
  • After that, select Values in the Look in option.
  • Finally, click on Find All button.

Find and Replace box to find out blank cells.

  • Thus 3 blank cells appear.
  • Pressing Alt+A, Select all the blank cells.

Selection of all the blank cells to remove from the dataset.

  • Lastly, delete all the rows containing blank cells and get the final result without blank cells.

Rows containing blank cells were removed.

📕 Read More: 4 Ways to Find and Replace Blank Cells in Excel

Method

6. Using Filter Feature

Filtering the entire dataset you can filter out and delete rows containing blank lines as well as blank cells. Please check out the necessary steps.

 ⬇️⬇️ STEPS ⬇️⬇️

  • Initially, select the entire table.
  • Secondly, Click on the Sort and Filter from the Data Menu.
  • Thirdly, click on the Filter option.

Use of Filter option to delete a row if any cell is blank in Excel

  • Now, a drop-down box appears beside every heading of the table.
  • Next, From the drop-down box, de-select blanks to filter data.

Deselecting blanks, remove blanks.

  • Finally, by doing similar action, rows containing blank cells are eliminated and filtered data comes out as the final result.

blank cells, as well as rows, removed.

Method

7. Remove Lines with Blank Cells By Sorting in Excel

Sorting the table of the dataset and deleting rows containing blank lines can be another way to remove blank lines. Please check out the below steps.

 ⬇️⬇️ STEPS ⬇️⬇️

  • Initially, select the entire dataset.
  • Secondly, Click on the Sort and Filter feature from the Data tab.
  • Thirdly, click on the Smallest to Largest option.

Sorting feature to remove blanks.

  • Therefore, blank lines take place at the bottom of the table.

Blank lines are separated.

📕 Read More: 10 Quick Tricks to Remove Blank Lines in Excel

Method

8. Shift Rows with Blank Cells to the Bottom and Delete

There is another way to remove the rows with blank cells in Excel and that is to shift them to the bottom anyhow and then remove them. We can shift rows with blank cells to the bottom by a helper column using the COUNTABLANK or COUNTA functions

Shifting Blank Rows to Bottom

8.1 Using COUNTBLANK Function

Counting blank cells and removing them accordingly is another way to erase rows containing blank cells. The COUNTBLANK function generally counts blank cells. Please follow the procedure.

⬇️⬇️ STEPS ⬇️⬇️

  • First, navigate a blank cell i.e. F6.
  • Second, insert the following formula containing the COUNTBLANK function.
=COUNTBLANK(B6:E6)

Use of COUNTBLANK function to count blank cells.

  • Third, auto-filling the cells, and get the number of blanks.
  • Now, select the entire data of the Blank column.
  • Then, Click on the Sort and Filter feature from the Data tab.
  • Next, click on the Smallest to Largest option.

Sorting the blanks.

  • Rows containing blank cells remain at the last 3 rows.

Blanks separation.

  • Finally, delete the last 3 rows and get the data without blank cells.

Blank rows eliminated.

📕 Read More: 4 Easy Ways to Fill Blank Cells in Excel with Formula

Shifting Blank Rows to Bottom

8.2 Using COUNTA Function

Using the function COUNTA which counts the values based on the presence of texts with TRUE FALSE. Please check out the necessary steps.

 ⬇️⬇️ STEPS ⬇️⬇️

  • Firstly, select a blank cell B6 at the first column of the table.
  • Secondly, type the following formula in the B6 cell.
=COUNTA(C6:XFD6)=0

COUNTA function to detect blank cells in Excel.

  • Thirdly, Using Fill Handle, fill the data with TRUEFALSE from B4 to B14 range.
  • Now, Select the entire dataset.
  • Then click on the Sort and Filter option from Data at the Top Ribbon of the workbook.
  • After that, select Sort [A to Z] for the Smallest to Largest option.

Auto-filling with TRUE-FALSE.

  • Finally, get the TRUE values in the last two rows.

Based on TRUE-FALSE, rows separation.

  • Therefore, by Deleting the last two rows, you will get the final output without blank cells.

Blank cells as well as rows were erased.

Method

9. Using VBA to Remove Rows with Blank Cells

VBA code to remove blank lines in Excel also can be a way. With the enabling of the Developer option, you can generate a program of your own and erase unwanted blank rows. Please follow the steps.

⬇️⬇️ STEPS ⬇️⬇️

  • First, Check if your Developer option is available or not.
  • Then to enable the Developer option, Right-click on the mouse at the Top Ribbon and select Customize the Ribbon from the list.

Enabling VBA option.

  • Click on Developer and click on the OK button.

Turn on the Developer option at the Top Ribbon.

  • Now Developer mode is on.
  • Next, Click on the Visual Basic option.
  • Then, to insert the VBA code, click on Module from the Insert menu.

Module to insert VBA code.

  • Insert the following code in the Module and click Run to execute the VBA code.

Code

Sub Ewmove_3()
Range("C6,D10,E13").Select
Range("E13").Activate
Application.CutCopyMode = False
Selection.EntireRow.Delete
End Sub

VBA code to remove lines from the Excel data sheet.

  • Lastly, blank lines including blank cells eliminated.

Blank lines were removed from the dataset.


📄 Important Notes

🖊️  Measure the full range of rows or columns accurately while using the COUNTA function.

🖊️  SORT function is only available on Office 365.

🖊️  During working with VBA code, check whether your Developer tool is available.


📝 Takeaway from This Article

📌  In this article, we demonstrated general steps from the context menu, keyboard shortcut, and use of the Delete option from the Top Ribbon to remove blank lines from a dataset.

📌  COUNTA function to count text’s availability.

📌  COUNTBLANK function finds out and counts the number of blank cells.

📌  Use of Find and Select option to find blank lines and erase them.

📌  VBA to remove blank lines.


Conclusion

We demonstrated every possible way to delete a row if any cell is blank in Excel. I hope you enjoyed your learning. Any suggestions as well as queries would be appreciated. For better understanding and new knowledge don’t forget to visit www.ExcelDen.com.


Related Articles

(Visited 51 times, 1 visits today)
MD Tanvir Rahman

MD Tanvir Rahman

Hello, I am Tanvir. Graduated from BUET. Had a year-long Experience at Operation of Vitacan Industries Ltd. I found Excel is the most unique software to reduce both time and paper. Now, I am an Excel enthusiast, love to play with data and publish content.

We will be happy to hear your thoughts

Leave a reply

ExcelDen
Logo