How to Delete Columns in Excel That Go on Forever (6 Ways)

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.

STEPS:

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

6 Ways to Delete Columns in Excel That Go on Forever

  • And by doing this, you will see that the columns that you don’t want to be in your sheet are no longer there.

6 Ways to Delete Columns in Excel That Go on Forever

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.

STEPS:

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

Using Keyboard Shortcut to Delete Columns That Go on Forever

  • And, that’s it! This will remove all the columns from your spreadsheet that you want to delete.

Using Keyboard Shortcut to Delete Columns That Go on Forever

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.

STEPS:

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

Eliminate Columns That Go on Forever by Using Ribbon in Excel

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


Similar Readings


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.

STEPS:

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

STEPS:

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

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.

Delete Extra Blank Columns with VBA Macros

  • Another way to open the Visual Basic Editor is just right-clicking on the sheet and selecting View Code.

Delete Extra Blank Columns with VBA Macros

  • Further, write down the VBA code there.

VBA Code:

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.

Delete Extra Blank Columns with VBA Macros

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)


Conclusion

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!


Related Articles

Sabrina Ayon

Sabrina Ayon

Hi there! This is Sabrina Ayon. I'm really excited to welcome you to my profile. Currently, I'm working in SOFTEKO as a Excel and VBA Content Developer. I'm a student of United International University and my program is Computer Science and Engineering. I love working with computers and solving problems. I’ve always been interested in research and development. So, here I will be posting articles related to Microsoft Excel. Hoped this may help you. Thank you.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo