A Final Thought Over the years, a wide range of add-ons have appeared online.Every now and then, the data that you import from webpages may result in a great number of columns appearing even if theyre not used.
You see this happening with CSV files and.txt files just as often. When it happens, deleting columns manually may not always be easy. Sure, if you only have two or three empty columns, its quite ok to delete them manually. Delete Excess Rows And Columns In Excel Code In TheUsing VBA Macro Quick Links Using VBA Macro Go to your Excel file Hold Alt and F11 together Wait for the Microsoft Visual Basic for Applications window to appear Click Insert Select Module Paste the following lines of code in the window Press F5 to compile and execute the macro Input the appropriate work range in the dialog window A1 Top corner J12 Bottom corner Press OK Using Excel Tools First select the data range Press F5 Click Special Select Blanks option Click OK (This selection will ensure that all blank cells are selected in the targeted range) Go to Home tab Select the Delete dropdown menu under the Cells tools group Select Delete Cells Select Shift Cells Left for removing and rearranging columns Click OK Select Shift cells up for removing and rearranging rows Other Easy to Perform Sorting Tasks A Final Thought The first method involves using a VBA macro. Go to your Excel file Hold Alt and F11 together Wait for the Microsoft Visual Basic for Applications window to appear Click Insert Select Module Paste the following lines of code in the window Sub DeleteEmptyColumns() Updateby20140317 Dim rng As Range Dim InputRng As Range xTitleId KutoolsforExcel Set InputRng Application.Selection Set InputRng Application.InputBox(Range:, xTitleId, InputRng.Address, Type:8) Application.ScreenUpdating False For i InputRng.Columns.Count To 1 Step -1 Set rng InputRng.Cells(1, i).EntireColumn If Application.WorksheetFunction.CountA(rng) 0 Then rng.Delete End If Next Application.ScreenUpdating True End Sub Press F5 to compile and execute the macro Input the appropriate work range in the dialog window The work range or data range is the specific interval between columns that you want to target. 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 notice that: A1 Top corner J12 Bottom corner You cant select the data range before you start uploading the macro as it wont stay selected. Press OK After that, all the empty columns should be erased and all filled columns should be next to each other. Using Excel Tools Obviously, Excel wouldnt be such a powerhouse if it didnt have great sorting abilities. You can use the Delete dropdown menu to delete entire rows, columns, or blank cells. First select the data range Press F5 Click Special Select Blanks option Click OK (This selection will ensure that all blank cells are selected in the targeted range) Go to Home tab Select the Delete dropdown menu under the Cells tools group Select Delete Cells Select Shift Cells Left for removing and rearranging columns Click OK Now the empty cells from the blank columns shouldve disappeared and all the other rows would be moved closer together. However, instead of moving the cells to the left you select the other option. Select Shift cells up for removing and rearranging rows Depending on what version of Excel youre running, you may get different wordings. But in any case, the top two options in the Delete Cells menu are always the same. This method no longer removes all blank cells in the selection. Before Excel 2013, this would inadvertently remove even empty rows which would generally mess up the sorting. Therefore, if you want to also get rid of the rows, you can do so by selecting the data range again and following the previous steps. Then simply select to shift or delete cells up instead of left. 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 menu, you can do so much more in Excel. Do you have certain formulas that are no longer relevant You can remove them as well or rearrange them accordingly. You can also remove unnecessary comments or all comments from your project if you dont want them to show up during your presentation.
0 Comments
Leave a Reply. |
Details
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |