10 Quick Tricks to Remove Blank Lines 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 delineated as blank lines in Excel. Now consider a data sheet that has blank lines in the Excel sheet. In this case, we must remove lines in Excel. There are several methods to remove blank lines, cells, or rows in Excel. In this article, we are going to learn to remove blank lines in Excel.

📁 Download Excel File

To practice please download the Excel file from here:

Learn to Remove Blank Lines in Excel with These 10 Approaches

To learn 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 including blank lines in the dataset. In this article, to remove blank lines in Excel, we are going to use the Context menu option, keyboard shortcut, COUNTA function, COUNTBLANK 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 rows altogether and to get a Context Menu by Right-Click on the mouse to delete manually. Please follow the procedure below.

⬇️⬇️ STEPS ⬇️⬇️

• Firstly, click on cells B6:E6, B10:E10, and B13:E13 holding Ctrl to select the cells altogether.

• Then Right-Click on the Mouse to get a Context Menu list.
• 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 lines and with the help of the keyboard key, one can easily remove any rows he wants. Please check the steps below.

⬇️⬇️ STEPS ⬇️⬇️

• Firstly, click on cells B6:E6, B10:E10, and B13:E13 holding Ctrl to select the rows along with cells.
• 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 B6:E6, B10:E10, and B13:E13 holding Ctrl to select lines.
• Then press Alt to get the keys.
• Next, press H for the Home menu.
• After that, press D the Delete feature.
• Further press R to select the Delete Sheet Rows option.

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

📕 Read More: 3 Approaches to Make Empty Cells Blank in Excel

Suppose you are asked to find out blank rows 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 menu.
• 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 list.
• 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 menu.
• 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 cell.
• 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 all the 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 feature.

• Now, a 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 menu.
• Thirdly, click on the Smallest to Largest option.

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

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 menu.
• Next, click on the Smallest to Largest option.

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

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

📕 Read More: 9 Quick Tricks to Delete Row If Cell is Blank in Excel

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 containing the COUNTA function.

• 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 the Data menu at the Top Ribbon.
• 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.

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

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

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

📌  Find and Select feature to find blank lines and erase them.

📌  VBA to remove blank lines.

Conclusion

We demonstrated every possible way to remove blank lines in Excel. I hope you enjoyed your learning. Any suggestions, as well as queries, are appreciated. For better understanding and new knowledge don’t forget to visit www.ExcelDen.com.

Related Articles

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