Md. Sourov Hossain Mithun
Nowadays, Excel is the most versatile tool for analyzing and visualizing data. Most people in the office and businesses use this software on a day-to-day basis. While working in Excel we may need to delete rows for many reasons. Like, there might be irrelevant data or blank cells. So, it’s a pretty necessary task. In this article, we’ll demonstrate 5 basic and easy methods to delete rows in Excel that go on forever. So, go through the entire article to understand the whole topic properly.
Not to mention, here, we have used Microsoft Excel 365 version, you may use any other version according to your convenience. Please leave a comment if any part of this article does not work in your version.
How to Delete Rows in Excel That Go on Forever: 5 Easy Methods
For ease of understanding, we are going to use Details of Products of a certain clothing store. This dataset includes the Product Code, Product Name, and Quantity in columns B, C, and D respectively. Using this dataset, we’ll try to achieve our goal. So, let’s explore them one by one.
Note: This is a basic dataset to keep things simple. In a practical scenario, you may encounter a much larger and more complex dataset.
Read More: How to Delete Rows Using Excel Shortcuts
1. Using Context Menu
In our very first method, we’ll use the manual method to delete rows in Excel. Let’s follow the steps below to do this using this method.
📌 Steps:
➽ At first, select the rows that you want to delete.
➽ Then, right-click your mouse.
➽ After that, select Delete from the Context Menu.
Immediately, you’ll notice that the selected rows are deleted now.
Read More: How to Delete Multiple Rows in Excel at Once
Similar Readings:
- How to Delete Filtered Rows in Excel
- How to Delete Unused Rows in Excel
- How to Find and Delete Rows in Excel
- How to Delete Every Other Row in Excel
- How to Delete Blank Rows in Excel
- Delete Row If Cell Contains Specific Values in Excel
2. Utilizing Delete Command from Ribbon
In this method, we’ll delete rows in Excel that go on forever using the Delete tab from the Home ribbon.
📌 Steps:
➽ Firstly, select the rows.
➽ Later, click as follows: Home > Cells > Delete > Delete Sheet Rows.
Then, instantly, you will notice that those rows are no more.
Read More: How to Delete Selected Rows in Excel
Similar Readings:
- How to Delete Row If Cell Is Blank in Excel
- How to Delete Empty Rows at the Bottom in Excel
- How to Delete Multiple Rows in Excel Using Formula
- How to Delete Multiple Rows in Excel with Condition
- How to Delete Rows in Excel without Affecting Formulas
- How to Delete Infinite Rows in Excel
3. Applying Find & Select Tool
Here, we’ll use the Find & Select tool to delete blank rows in Excel that go on forever. This is useful when you’re working with large spreadsheets or when you need to change certain text repeatedly. For this method, I have placed two blank rows in my dataset.
📌 Steps:
➽ At this time, select the data range (B4:D11).
➽ Correspondingly, click Home > Editing > Find & Select > Go To Special….
Suddenly, a dialog box named “Go To Special” will appear.
➽ Mark on the Blanks option.
➽ Press OK.
The blank rows are now highlighted.
➽ Then right-click your mouse and select Delete from the Context Menu.
A new Delete dialog box will open up.
➽ Mark the choice of the Entire row and press OK.
Now you’ll spot that the blank rows are gone forever.
Read More: Delete All Rows Below a Certain Row in Excel
Similar Readings:
- How to Delete All Rows Not Containing Certain Text in Excel
- How to Delete Rows Based on Another List in Excel
- How to Remove Rows Containing Identical Transactions in Excel
- [Fixed!] Not Enough Memory to Delete Rows Error in Excel
- How to Delete Row Using VBA
- How to Delete Row with VBA Macro If Cell Is Blank in Excel
- How to Delete Entire Row Based on Cell Value Using VBA in Excel
4. Implementing Filter Option
Filtering data in Excel refers to displaying only the rows that meet certain conditions. We’ll apply it here in our last method to remove blank rows in Excel.
📌 Steps:
➽ Initially, select the data range including the title.
➽ Then, click in the sequence: Data > Filter.
Then, you will see the filter icon beside every heading of your dataset.
➽ At this moment, press any filter icon, and the filtering options will open up.
➽ Then, unmark everything except (Blanks).
➽ As usual, press the OK button.
As a result, it is showing only the blank rows now.
➽ Afterward, select those blank rows and right-click your mouse.
➽ Now, click on Delete Row from the Context Menu.
Magically, the blank rows are deleted. Now, we’ll execute the following steps to get back the other rows.
➽ This time, click on the filter icon again.
➽ Then, mark the (Select All) option.
➽ Finally, press OK.
Our filled rows are back now.
Related Content: How to Delete Rows in Excel with Specific Text
Similar Readings:
- How to Delete Row If Cell Contains Value Using Macro in Excel
- How to Delete Row Using Macro If Cell Contains 0 in Excel
- VBA to Delete Every Other Row in Excel
- How to Delete Multiple Rows with VBA in Excel
- How to Delete Rows in a Range with VBA in Excel
5. Incorporating VBA Macro
You might be wondering, is there a way to automate this task? Then VBA has you covered. Just follow along.
📌 Steps:
➽ Firstly, go to Developer tab >> Visual Basic option.
Then, the Visual Basic Editor will open up.
➽ Secondly, go to Insert tab >> Module option.
After that, a Module will be created.
➽ Thus, paste the following code into the module.
Sub Dlt_Row()
Rows(8).EntireRow.Delete
Rows(6).EntireRow.Delete
End Sub
➽ Now, click on the Run icon or press the F5 key on the keyboard.
Magic! Look at the worksheet now. The desired rows are gone.
Related Content: How to Remove Highlighted Rows in Excel
How to Delete Columns That Go on Forever in Excel
Like before, we also can delete columns in Excel that go on forever. It’s simple and so easy. Just follow along.
📌 Steps:
➽ At first, select columns C and D.
➽ Then, right-click anywhere inside these two column headings.
➽ After that, select the Delete option from the Context Menu.
Look, they are gone now.
Practice Section
For doing practice by yourself, we have provided a Practice section like the one below on the right side of the sheet. Please do it by yourself.
Related Content: How to Delete Hidden Rows in Excel
Download Practice Workbook
You may download the following Excel workbook for better understanding and practice.
Conclusion
This article explains how to create an invoice in Word from Excel data in a simple and concise manner. Thank you for reading this article. We hope this was helpful. Please let us know in the comment section if you have any queries or suggestions.
Related Articles
- Applying VBA Code to Delete Rows Based on Multiple Cell Value
- Excel VBA to Delete Rows with Specific Data
- How to Delete Selected Rows with Excel VBA
- How to Use VBA to Delete Empty Rows in Excel
- How to Use Macro To Delete Rows Based on Criteria in Excel
- How to Filter and Delete Rows with VBA in Excel
- How to Delete Unfiltered Rows in Excel Using VBA
- How to Delete Hidden Rows in Excel VBA
