How to Delete Blank Rows and Columns in Excel

Here we can see, “How to Delete Blank Rows and Columns in Excel”

Notwithstanding, you can cancel them by right-clicking each once single-handedly and Selecting “Cancel,” a real-time-consuming task If you have a lot of blank rows in your Excel spreadsheet. Notwithstanding, there’s a fast and easier way of deleting both blank rows and blank columns.  

How To Delete Blank Columns In Microsoft Excel 

Using VBA Macro

 The first manner involves using a VBA macro. 

1.  Go 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.  Past the following lines of constitution 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

7.Press F5 to compile and execute the macro. 

8.Input the felicitous work range in the dialog window. 

The working 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 ciphering correspond to the rows.

Notwithstanding, you’ll notice that , If you drag this out with your mouse or by holding Shift and using the arrow keys. 

$A$ 1 – Top corner 

$J$ 12 – The bottom corner  

You can’t select the data range before uploading the macro as it won’t stay selected. 

9.Press OK. 

After that, all the empty columns should be eradicated, and all filled columns should be following each other. 

Using Excel Tools to Delete Blank Columns

Excel wouldn’t be such a hustler if it didn’t have great sorting competencies. You can use the Cancel dropdown menu to cancel entire rows, columns, or blank cells. 

1. First Select the data range and press F5. 
2. Then, click Special. 
3. Now, Select the Blanks option. 
4. Click OK (This selection will ice that all blank cells are Selected in the targeted range). 
5. Go to the Home tab. 
6. Select the Cancel dropdown menu under the Cells tools group. 
7. Select Cancel Cells. 
8. Select Shift cells left for removing and rearranging columns. 
9. Click OK. 

The empty cells from the blank columns should’ve disappeared, and all the other rows would be closer together. 

 You can use the same approach to delete entire rows. Notwithstanding, instead of moving the cells to the left, you tag the other option. 

Select Shift cells up for removing and rearranging rows. 

Depending on what rendition of Excel you’re running, you may get different wordings. But in any case, the top two options in the delete Cells menu are always the same. 

This form no longer removes all blank cells in the selection. Before Excel 2013, this would inadvertently remove yea empty rows, which would generally mess up the sorting. 

Now the problem no longer occurs. So, if you also want to disburden the rows, you can do so by choosing the data range again and following the anterior line. Either choose to shift or delete cells up instead of left. 

Deleting Blank Columns on Android

Although the process is cognate for deleting blank columns in Excel on Windows, Mac, and Android, presently’s a quick overview of Android. 

1. Open up the Excel spreadsheet you want to edit and top on the column heading you want to edit. 
2. Now, Select Delete from the menu that appears. 

How to Delete Blank Rows in Excel 

Blank rows allude to deficient or missing data. A blank row or, yea, a blank cell in the wrong place can make Excel misapprehend data ranges and beget breaches when a function is applied. Also, blank rows can be a visual annoyance. 

Blank rows aren’t always bad, but it’s easy to remove them. All Excel abecedarians should learn countable approaches to remove blank rows in their spreadsheets. 

How to remove blank rows in Excel manually

When there are a small number of rows, you can fast spot the blank rows and remove them with a selection. 

1. Press the entire row by tagging the row number on the left side of the screen. You can tag multiple rows by pressing Ctrl and clicking on the row number. 
2. Right-click anywhere on this row and choose Delete. The empty rows will dissolve, and those below the deleted bones will be moved up to replace the deleted space. The row calculation will also contemporize to stay in sequence. 

Use a simple sort to find and remove blank rows in Excel

Sorting your data won’t only help organize your data, but this can also bring out blank rows you don’t need. A simple like will bunch all blank rows together and help you fast remove them. 

Select the range of rows in the spreadsheet. 

1. Go to the Data tab. 
2. Go to the Sort& Filter group. Presently you can Select either the Like A to Z or Like Z to A button to sort near ascending or descending order. Blank rows will be sorted at the bottom of the selected range. 
3. To get back the original order, you can add an indicator column before you sort the data. Re-sort the data according to this needle column and then delete it. 

Use the Find command to search and remove blank rows in Excel

Using the simple delete command is enough when you have numberless blank rows in your spreadsheet. But it gets ponderous if you have to scroll around to find and either opt for multiple blank rows. 

The Find and Replace command is under the Find & Select button on the Home tab. Use it to find all empty rows in breadth or the entire workbook and either delete them in one go. You can also use the Ctrl F keyboard alley to bring up the Find and Replace dialog box. 

1. Open the dialog in the Find tab. Keep the Find what field blank. 
2. Take the Options button and check the box to Match the entire cell contents. 
3. For this example, We use these options to limit the search within the Sheet, search By Rows, and look in the Values. 
4. Click the Find All button to get all the blank cells. 
5. All blank rows are selected. Right-click on any selected row and choose Delete from the menu. 

Use Excel’s Find & Select command to remove blank rows quickly

 The Find & Select command is just under the Find and Replace commands. It also works in an alike way. 

Use the Find & Select command to select all blank rows fast and remove them in one click.

1. Select the range of rows and columns on the spreadsheet that has all the blank cells. You can select the entire range of data cells on your field too. In our instance, we will Select the data range that extends from Column A to ColumnF. Blank cells are shown in gutless for easier readability.
2.  Go to Home> (Editing Group) Find & Select > Go To Special. 
3.  In the Go to Special dialog, choose the radio button for Blanks. Click on OK. 
4.  All blank rows in the chose range are emphasized. Right-click anywhere on these rows and Select Cancel from the medium menu. Excel will display four options in a bitty dialog to decide how to handle the empty spaces after deleting the blank rows. 
5.  In our prototype, Select the Entire row as the whole row is blank. However, you can determine for Shift cells up to make the non-empty rows move up into the vacant space If there’s some data in the flanking cells. But you should only shift cells if all the data line up as they’re meant to be. 

Use a simple filter to remove blank rows in Excel

 All of the whilom tacks will help you manage simple blank rows where all cells are empty. But real-world Excel wastes can have rows where only some rows are blank while others have useful data (Row 8 and Row 11). 

 In those cases, the anterior manners won’t be equal to discern between the two rows and remove the rows with some blanks (and data). You have to be pickier about the rows you remove with the help of a disgrace. 

1. Select all columns that hold your data range. 
2.  Go to Ribbon > Data tab > Sort & Filter Group> Filter. 
3.  Move across the columns. Click the Filter dropdown for each column. Uncheck Select All either check Blanks for rows that have only some blank cells. 
4.  Go across the other columns and repeat the scum for blanks in the other columns to particularly find those entire blank rows. 
5.  Tag the filtered rows. Go to the Home tab and click Delete > Delete Stretch Rows. Otherwise, tag and right-click on the fully blank rows. Either choose Cross from the mise-en-scène menu as anteriorly. 
6.  Go back to the Data tab and switch off the Filter.  

Use an advanced filter to remove blank rows in Excel

 In Excel, you can use advanced dirt with a criterion or a condition that matches all values against it and excerpts a subset of the entire data. In this case, it’ll corkscrew all rows which don’t have any rows with the blank cells.  

 The advanced cleaner will corkscrew the same data to another part of the breadth minus the blank cells. This methodology requires some inaugural setup. But it can also pull the specific data you want from a large table. 

1.  Type “<>” ( Less than… greater than) in the criterion cell F2. 
2.  Go to Data> Sort & Filter> Advanced Filter. 
3.  Select Copy to another locations. Enter the List range of your data and the range of cells where you want to pull the results to. 

 In this example 

•  The List range is $A$1$D$10. 
•  The Extract range where we will copy the cells to is $H$1$K$1. 
•  The Criterion range is $F$1$F$ 2. 

 The uprooted data on the right doesn’t contain blank cells. 

 To uproot rows that have blank cells, you can use the equal sign. Type it as = ” = ” as the criterion. As you can see, we’ve yanked records for Carl Johnston and Chester Lloyd from the table. 

 Conclusion 

 I hope you start this guard useful. However, do not wobble to use the form below, If you have any questions or play-by-play. 

 User Questions 

1. Why will blank rows not delete in Excel? 

 Press the vital Shift Space on your keyboard. Press the Ctrl-Shift Down Arrow key, and rows will get tagged till the last row. Press Ctrl on the keyboard to delete the blank rows. 

2. How do you delete thousands of blank rows in Excel? 

 You can remove blank rows in Excel by first doing a “Find& Tag” of blank rows in the document. You can either delete them all at once using the “Delete ” button on the Home tab. 

3. Can not delete multiple rows in Excel? 

 If the data is in the form of a functionary’ table’, you will not be competent to choose different selections of multiple rows to delete in one go. You have to copy and bomb the data into an empty worksheet (without the’ table’ formatting), and you should be competent to do what you describe. 

4. How do I delete all the empty rows (not just the contents) under my data set that I do not need? 

How do I delete all the empty rows (not just the contents) under my data set that I don’t need? from excel

5.how to copy data and remove blank rows using only formulas? 

How to copy data and remove blank rows using only formulas? from excel