How to remove blank or empty rows in Excel?
If there are hundreds of blank rows scattering through the whole worksheets, you will be frustrated about it. This tutorial is giving instructions on how to remove all the blank rows step by step.
Remove all the blank rows with Go To Special command
Remove all the blank rows with VBA code
Remove all the blank rows with only one click
Recommended Productivity Tools
Office Tab: Bring handy tabs to Excel and other Office software, just like Chrome, Firefox and new Internet Explorer.Try now
Kutools for Excel: 200 new features for Excel, make Excel much easy and powerful, increase productivity immediately.Try now
Remove all the blank rows with Go To Special command
1. Select the range containing blank rows you want to remove.
2. Click Home > Find & Select > Go To Special.
3. In the Go To Special dialog box, check the Blanks option.
4. And then click OK, all of the entire blank rows are highlighted. Then click Home > Delete > Delete Sheet Rows, the blank rows have been deleted from the selected range. See screenshots:
 |
 |
 |
Remove all the blank rows with VBA code
The following VBA code also can help you to delete the blank rows of the selected range. Please do as this:
1. Select the range that you want to remove the blank rows.
2. Click Developer > Visual Basic, a new Microsoft Visual Basic for applications window will be displayed, click Insert > Module, and input the following code into the Module:
VBA: Remove blank rows of the selected range.
Sub DeleteBlankRows()
'Update 20131107
Dim Rng As Range
Dim WorkRng As Range
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
xRows = WorkRng.Rows.Count
Application.ScreenUpdating = False
For i = xRows To 1 Step -1
If Application.WorksheetFunction.CountA(WorkRng.Rows(i)) = 0 Then
WorkRng.Rows(i).EntireRow.Delete XlDeleteShiftDirection.xlShiftUp
End If
Next
Application.ScreenUpdating = True
End Sub3. Then click 
button to run the code. And the entire blank rows in the selection will be removed.
Remove all the blank rows with only one click
You can use the third party add-in Kutools for Excel to quickly merge multiple columns. What is Kutools for Excel?
Kutools for Excel: with more than 120 handy Excel add-ins, free to try with no limitation in 30days. Get it Now.
1. Click Kutools > Delete > Delete Blank Rows, see screenshot:
2. Then you can choose the scope to delete the blank rows. You can delete the blank rows in the selected range, in active sheet, in selected sheets and in all sheets of Excel. For more detail information about this utility, please visit here.
Tip: If there is a row which looks like a blank row but it contains invisible characters in its cells, this utility cannot delete such blank rows.
Remove all the blank rows with only one click
Kutools for Excel includes more than 120 handy Excel tools. Free to try with no limitation in 60 days. Download the free trial now!
Recommended Productivity Tools
Office Tab
Bring handy tabs to Excel and other Office software, just like Chrome, Firefox and new Internet Explorer.
Kutools for Excel
Amazing! Increase your productivity in 5 minutes. Don't need any special skills, save two hours every day!
200 New Features for Excel, Make Excel Much Easy and Powerful:
- Merge Cell/Rows/Columns without Losing Data.
- Combine and Consolidate Multiple Sheets and Workbooks.
- Compare Ranges, Copy Multiple Ranges, Convert Text to Date, Unit and Currency Conversion.
- Count by Colors, Paging Subtotals, Advanced Sort and Super Filter,
- More Select/Insert/Delete/Text/Format/Link/Comment/Workbooks/Worksheets Tools...
Comments
It's listed below your question.
Only SOME of the rows (1,242 in this case) were blank all the way across.
The PROBLEM was that selecting BLANK cells would DELETE ENTIRE ROWS that had data in other columns.
I found a perfect solution.
I created a fourth column in which I used the COUNTA function.
The formula went =COUNTA(A1:C1) and then copied it down thru several thousand rows of data.
So, my D column had a stack of 3s,2s,1s & 0s.
Then I used the FILTER command on Column D to only show the rows that equalled 0. The result was a sheet of empty cells with 0s in column D... ONLY the totally blank rows had been selected!
Then I clicked and dragged down the left-side ROW NUMBERS (those in the margin) to select those rows, right-clicked and selected DELETE. They all vanished.
When I turned OFF the filter, I had a perfect set of cleaned data.
In less than a minute! Done!
= = = = = = =
That was so easy, I then got fancy and changed the COUNTA function to only select items from the A column that had data in them. (Those rows happened to be "Title" rows for clumps of data below them.) It read =COUNTA(A1) and copied IT down the D column to the end. The result was a column of 1s and 0s.
When I turned FILTER on again and selected for "1", only the Title rows appeared. I selected those rows and BOLDed them. Then turned the Filter off, and again, perfect formatting down thru thousands of lines.
LOVE THAT FILTER FUNCTION!
Thanks
Thank you very very very much
Ned
What ever i did all rows stay undeleted in my spreadsheet. After ctrl-End I am again at T448140.
Your first step in the extra column (which should be "U" column for the filtering), the formula in U147533 should read (judging from your supplied data) =COUNTA(A147533 :T147533) and then COPY that down to row 448140. You should get a column of numbers, SOME of which will be zeroes.
Then click on the "U" letter at the top of the column (in the ruler margin) to highlight the entire column.
Then select the Filter function (Data tab, Filter), click on the little pull-down triangle, and delete all the checkmark choices except the one beside the "0" choice (OR the "Blanks" choice, if visible). Click on OK.
You should then see only rows that have zero data in them.
Then as described previously, with the Filter function still active, highlight the rows from 147533 to 448140 that are containing those selected row numbers (by clicking/draggi ng down the left ruler margin OR clicking top one and shift-clicking last one), and DELETE them. (Right-click, Delete).
When they vanish, turn the Filter function off and all other data will reappear.
Good luck!
Ctrl-End I come to cell AG56367. So I have all blank rows from 52223 to 56367.
After COUNTA(a2;af2) copied to row 56367 and highlighting rows 52223 to 56376 on Right-click EXCEL offers DELETE ROW and I deleted this rows.
After that I turn off Filter: with Ctrl-Home I come to cell A1, with Crtl-End I come to cell AG56367. So all was the same
(That's if you're trying to delete ONLY all-blank rows, not partially-blank ones.)
Really, the whole operation takes less than two minutes if you read over the instructions step by step. Maybe try displaying steps side-by-side with the work you're doing (or print the steps out, then follow along). Once you see it work the first time, you'll feel a lot more comfortable with it, and you'll definitely be wiser in the operation of the Filter function.
Re the folks you're doing the work for, just bring them the results; they probably don't care "how", they just wanna know "what". A quickly-cleaned -up database will look more impressive than watching how it got that way, especially if you're just learning how. Give it a practice run or two. Oh, and make a backup copy, of course, just in case Undo fails or something while trying it out.
(Mind you, at your first query, I envisioned a macro that would have to work in all environments. What YOU would find is that once you've got a fixed-structure data set going, you'll know exactly how the procedure works and could just record steps into a macro of your own, just for the speed factor.)
--helped me greatly! Thanks!
Example : Columns : A to F having 5 rows of data as follows . When we do the blank operation we get unexpected reults (result doesn't show 5 rows..!!)
A B C D E F
Check1 Check2 Check3 Check4 Check6 Check7
Check1 Check2 Check3 Check4 Check7
Check1 Check2 Check3 Check4 Check7
Check1 Check2 Check3 Check4 Check6 Check7
Check1 Check2 Check3 Check4 Check6 Check7
any idea , why?
thanks
If i follow above the procedure, getting error message as 'Selection is too large'. Then how to remove empty cells for large list.
The first thing I would try is selecting half the database, or a quarter. If that works, repeat on remaining sections. If it does not, see if your computer has enough RAM available for processing such a large chunk of data. Without actually seeing the file, it's hard to say.
Ctrl-End I come to cell AW7335. So I have all blank rows from 2374 to 7335.
So, what for is Ctrl-End?
I can upload my file so anybody can delete blank rows for me.
i can try
Quoting Darko Loncar:
Ashwani
ashwanibahuguna
I then have in row 3 4 and 5 the choice of, no action, low action and high action.
I review the problems and enter an "x" in the appropriate column.
I want to make a condensed list of only the items with an "x" in the high action column but I also need to keep the complete spreadsheet so can just filter.
is there a reference/index /lookup type function I can use?
It's really great help me
Isn't this titled "How to remove blank or empty rows in Excel?" not "How to remove rows with blank cells?"
The inquirer did not want any row removed that had some data in it... only rows that were completely blank. And the problem with the BLANK command was that it took out ANY rows entirely that had even one blank cell, erasing the rest of the data on the same row.
Try it. It does the opposite of what you stated in your first sentence.... it takes out only entire blank rows, and leaves ALL the remaining data intact.
Sub DeleletBlankrows()
Range("A1:A10000").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub
I just selected the columns needed and make sure the merge button was unselected. From there, the F5 special blanks select worked!
Hope it helps someone.