How To Get Rid Of Extra Columns In Excel

There are multiple means to delete blank columns in a Microsoft Excel file. Why would you lot need to exercise this? – Simple.

Every now and so, the data that you import from webpages may result in a great number of columns appearing even if they're not used. Y'all come across this happening with CSV files and .txt files but as frequently.

When it happens, deleting columns manually may not always exist like shooting fish in a barrel. Sure, if you lot merely take 2 or three empty columns, it's quite ok to delete them manually. But what if your imported projection creates 57 empty and not-continuous columns? – For that, you'll need an automatic process.

Using VBA Macro

The first method involves using a VBA macro.

1. Become to your Excel file.
   
2. Hold Alt + F11 together.
   
3. Wait for the Microsoft Visual Basic for Applications window to appear.
   
4. Then, click Insert.
   
5. Select Module.
   
6. Paste the post-obit lines of code in the window.
   Sub DeleteEmptyColumns()
'Updateby20140317
Dim rng Equally Range
Dim InputRng Equally Range
xTitleId = "KutoolsforExcel"
Set up InputRng = Application.Choice
Gear up InputRng = Application.InputBox("Range :", xTitleId, InputRng.Address,Type:=8)
Application.ScreenUpdating = Fake
For i = InputRng.Columns.Count To i Step -one
Gear up rng = InputRng.Cells(ane, i).EntireColumn
If Application.WorksheetFunction.CountA(rng) = 0 And so
rng.Delete
End If
Next
Application.ScreenUpdating = True
End Sub
   
7. Press F5 to compile and execute the macro.
   
8. Input the advisable work range in the dialog window.
   

   The work range or data range is the specific interval between columns that you want to target. The format is $A$1:$J$12. The letters correspond to the column and the numbers correspond to the rows.

   If you drag this out with your mouse or by holding Shift and using the arrow keys, you will find that:

   $A$1 – Peak corner
   $J$12 – Bottom corner

   You can't select the data range before y'all start uploading the macro as information technology won't stay selected.

9. Press OK.
   

After that, all the empty columns should be erased and all filled columns should be next to each other.

Using Excel Tools to Delete Blank Columns

Obviously, Excel wouldn't be such a powerhouse if information technology didn't have great sorting abilities. You can employ the Delete dropdown card to delete entire rows, columns, or blank cells.

1. Commencement select the information range and press F5.
2. Then, click Special.
   
3. Now, select the Blanks option.
   
4. Click OK (This choice will ensure that all bare cells are selected in the targeted range).
5. Go to Dwelling tab.
6. Select the Delete dropdown carte under the Cells tools group.
7. Select Delete Cells.
8. Select Shift cells left for removing and rearranging columns.
9. Click OK.
   

Now the empty cells from the blank columns should've disappeared and all the other rows would be moved closer together.

You can use the same arroyo to delete unabridged rows. However, instead of moving the cells to the left you lot select the other option.

Select Shift cells up for removing and rearranging rows.

Depending on what version of Excel y'all're running, you lot may go different wordings. But in whatsoever example, the top two options in the Delete Cells carte du jour are always the same.

This method no longer removes all bare cells in the pick. Earlier Excel 2013, this would inadvertently remove even empty rows which would mostly mess up the sorting.

Now the problem no longer occurs. Therefore, if you want to likewise become rid of the rows, you tin do then by selecting the data range again and post-obit the previous steps. And then but select to shift or delete cells up instead of left.

Deleting Blank Columns on Android

Although the process is like for deleting blank columns in Excel on Windows, Mac, and Android, here's a quick overview of doing it on Android.

1. Open up the Excel spreadsheet y'all desire to edit and top on the column heading you desire to edit.
2. Now, select Delete from the carte du jour that appears.

Other Easy to Perform Sorting Tasks

Although technically using the Excel toolbar to delete empty columns and rows seems easier, the VBA macro method is foolproof, which means you can use it even in older Microsoft Excel versions.

Using the same VBA module or Go To function bill of fare, you lot tin do and then much more in Excel. Exercise you have sure formulas that are no longer relevant? – You can remove them also or rearrange them accordingly.

You tin as well remove unnecessary comments or all comments from your project if you don't want them to show up during your presentation. Look into VBA if you desire to be an Excel power user.

A Terminal Thought

Over the years, a wide range of add-ons have appeared online. Some of them let you to take even more shortcuts when sorting big spreadsheets. However, these apps are rarely gratuitous and non worth the problem for simple tasks such as removing blank rows, cells, and columns.

Besides, if it were that difficult, Microsoft would've further simplified the process past now or created even more than extensive guides on Excel sorting.

Source: https://www.alphr.com/delete-blank-columns-excel/

Posted by: sandersbestinge1986.blogspot.com

Share this post