While working with Microsoft Excel, sometimes if we put formula and want that formula into the particular columns, we drag the formulated cell into other columns or double-click or press Ctrl + R to copy that formula into other cells. But many times that formula generates into other cells automatically and goes on forever. In this article, we will demonstrate how to delete columns in excel that go on forever.
Download Practice Workbook
You can download the workbook and practice with them.
6 Ways to Delete Columns in Excel That Go on Forever
There are so many ways to delete columns in excel, but it’s quite tricky work to delete columns that go on forever. Let’s see different ways to delete columns that go on forever in excel.
1. Delete Columns in Excel That Go on Forever by Right-Clicking
To delete columns that go on forever, we are going to use a dataset that contains some value with a formula. Let’s follow the simple steps down.
- Firstly, to select those columns that go on forever, press Ctrl + Shift + Right Arrow.
- This will take you to the end of your dataset.
- Then, manually select the columns that you want to delete.
- After that, right-click on your mouse.
- And choose Delete.
- And by doing this, you will see that the columns that you don’t want to be in your sheet are no longer there.
Read More: How to Delete Column and Shift Left Using VBA in Excel (5 Methods)
2. Using Keyboard Shortcut to Delete Columns That Go on Forever
The keyboard shortcuts save our time and make the work faster. We can use a keyboard shortcut to delete columns that go on forever. For this, we have to follow the easy procedure below.
- By the same token, as the previous methods, first, to go to the end of your data column, you have to press Ctrl + Shift + Right Arrow.
- Secondly, select the columns manually by dragging them over.
- Next, using the keyboard shortcut, press “Ctrl” and “–” together.
- And, that’s it! This will remove all the columns from your spreadsheet that you want to delete.
Read More: VBA to Delete Column in Excel (9 Criteria)
3. Eliminate Columns That Go on Forever by Using Ribbon in Excel
We can delete the columns that go on forever by using the ribbon in Excel. Let’s go through the steps down.
- In the beginning, likewise, the previous methods, go to the last column on your dataset, to do that, press Ctrl + Shift + Right Arrow.
- After that, select the columns that you want to remove from your spreadsheet.
- Then, go to the Home tab from the ribbon.
- Next, under the Cells category, click on Delete Sheet Columns from the Delete drop-down menu.
- And, there you go! This will eliminate all the columns that go on forever.
Read More: How to Delete Extra Columns in Excel (7 Methods)
- How to Delete Columns without Losing Formula in Excel (3 Easy Steps)
- [Solved!] Can’t Delete Extra Columns in Excel (3 Solutions)
- How to Delete Infinite Columns in Excel (4 Methods)
- Delete Columns Based on Header Using VBA in Excel
- Can’t Delete Blank Columns in Excel (3 Problems & Solutions)
4. ‘Go To Special’ Feature to Delete Blank Columns in Excel
Excel has so many amazing features, which help us to work fluently. Go To Special is one of those. With this feature, you can delete all the blank cells, columns, or rows. Also, you can remove the cells, columns, or rows that have a formula. Again, you can eliminate all the constant values and so on. Let’s delete all the formulated columns that go on forever. To do this, you have to follow the procedure below.
- Firstly, go to the Home tab from the ribbon.
- Secondly, in the Editing group, select Find & Select drop-down menu.
- After that, click on Go To Special.
- This will open the Go To Special dialog box.
- As we want to remove all the blank columns. So we choose Formulas.
- Then, click on the OK button.
- This will appear in a dialog box named Delete.
- Now, select the Entire Column from the Delete selection box.
- Finally, click OK.
- We will see the Delete dialog box.
- Further, select the Entire column.
- Then, click on the OK button.
- This will remove all the blank columns that go on forever.
Read More: Delete Blank Columns in Excel (3 Ways)
5. Stop Auto Generating Columns in Excel
While using a formula on excel, sometimes all the other columns automatically generate the formula. To stop auto-generating columns, let’s take a look at the steps down.
- First, as similar as before, go to the end of the columns by pressing Ctrl + Shift + Right Arrow.
- Further, go to the Home tab.
- Then, from the Cells category, click on the Format drop-down menu bar.
- And, next, select Hide Columns from the Hide & Unhide drop-down list.
- And, that will hide all the columns and those columns can not be automatically generated.
Read More: How to Delete Columns in Excel Without Affecting Formula (Two Ways)
6. Delete Extra Blank Columns with VBA Macros
We can use VBA Macros to delete all the blank columns in our spreadsheet. It’s a simple VBA code. So let’s do this with some easy steps.
- In the first place, go to the Developer tab from the ribbon.
- After that, click on Visual Basic to open the Visual Basic Editor.
- Or, press Alt + F11 to open the Visual Basic Editor.
- Another way to open the Visual Basic Editor is just right-clicking on the sheet and selecting View Code.
- Further, write down the VBA code there.
Sub Delete_Blank_Columns() Selection.SpecialCells(xlCellTypeBlanks).EntireColumn.Delete End Sub
- Now, press on the F5 key or click on the Rub Sub button to run the code.
Note: You don’t need to modify the code. You can copy and paste the code on your Visual Basic Editor. If you want to delete the blank rows. Simply use the code:
Sub Delete_Blank_Rows() Selection.SpecialCells(xlCellTypeBlanks).EntireRow.Delete End Sub
- In the end, we can see the required result.
Read More: Macro to Delete Columns in Excel (10 Methods)
The above methods will assist you to delete columns in excel that go on forever. Hope this will help you! If you have any questions, suggestions, or feedback please let us know in the comment section. Or you can have a glance at our other articles in the ExcelDemy.com blog!