Delete Blank Rows in Excel 2010
Blank rows in a Excel spreadsheet can make working with a list of data more difficult. Basic processes such as sorting, filtering and going to the end of your list will not always work as expected.
Fortunately there are many ways of identifying and removing blank rows in Excel 2010. Let's have a look at some of them.
Using Go To Special
One way of removing blank rows in a list is to select them first using the select blanks feature of Go To Special. The rows can then be deleted.
- Select the column where you want to look for blanks. This should ideally be an ID or other reference field that you know should never be blank.
- Click the Home tab on the Ribbon.
- Click the Find & Select button and select Go To Special.
- Select Blanks and click OK.
- The blank cells of the column are selected. Click the Delete list arrow and select Delete Sheet Rows.
Using the Filter
Blank rows can also be deleted by using the Filter feature in Excel. Filter the list to display the blanks and then delete them.
- Select the whole list of data.
- Click the Filter button on the Data tab of the Ribbon. The filter arrows appear across the header row.
- Click the filter arrow for the column you want to use to search for blanks
- Uncheck the Select All box and check the Blanks option.
- Click Ok. The filter runs and displays only the blank rows from the list.
- Select the blank rows. These can be identified by the blue row numbering.
- Click the Home tab of the Ribbon.
- Click the Delete list arrow and select Delete Sheet Rows.
Macro to Delete the Blank Rows
The quickest and most efficient way of deleting blank rows from a spreadsheet is to create a macro. Deleting blank rows will be as easy as clicking a button.
This macro will check that the row is blank as opposed to a single cell like the previous examples.
- Open the Visual Basic Editor by pressing Alt + F11
- Insert a module by clicking the Insert menu and selecting Module
- Enter or copy and paste the code below into the window
In the code below comments are used to explain each section of the code.
Dim lastrow As Long
Dim ISEmpty As Long
‘Count how many records in the list. This is done so that the Do loop has a finish point.
lastrow = Application.CountA(Range(“A:A”))
‘Start at the top of the list
‘Loop until the end of the list
Do While ActiveCell.Row < lastrow
‘Assign number of non empty cells in the row
ISEmpty = Application.CountA(ActiveCell.EntireRow)
‘If ISEmpty = 0 then delete the row, if not move down a cell into the next row
If ISEmpty = 0 Then
More by this Author
Microsoft Excel can do some amazing things. I like to attempt little projects testing what Excel can do. I was asked the other day to simulate a cup draw using Excel and this is the result. This article will give you...
Form controls can be added to an Excel spreadsheet to create interactivity with the user. This article looks at using option button controls to allow a user to choose the data they want to see on a chart. The first...
Although Excel’s greatest strength is its ability to analyse huge amounts of data, it is also great for creating forms. We can create formulas, apply validation rules and use techniques to make our forms user...
No comments yet.