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.

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.

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

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

• Therefore, rows containing blank cells are erased.

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.

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

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.

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

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.

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

• Finally, blank cells get highlighted.

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

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

• Therefore, rows containing blank cells are removed.

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.

• 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.

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

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

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

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.

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

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

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.

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

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

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

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.

• 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.

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

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

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

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.

• Thirdly, Using Fill Handle, fill the data with TRUE–FALSE 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.

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

• Therefore, by Deleting the last two rows, you will get the final output without 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.

• Click on Developer and click on the OK button.

• 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.

• 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

• Lastly, blank lines including blank cells eliminated.

📄 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

• 3 Approaches to Make Empty Cells Blank in Excel
• 4 Quick Tricks to Fill Blank Cells in Excel with N/A
• How to Determine and Count If Cell Is Not Blank in Excel
• 4 Quick Ways to Insert 0 If Cell Is Blank in Excel
• How to Get Blank Cell Instead of Zero Using Formula in Excel
• 4 Ways to Leave Blank If Cell Contains Zero with Excel Formula