Blank rows in your Excel spreadsheet are undesirable and can make it difficult to work with. Because of this, Excel provides many ways that we can locate and then delete the blank rows.
These techniques can include sorting the list to group the blank rows together, filtering for blanks and using Go To Special to locate blank cells for deleting.
However these can be time consuming and may not be the best approach for you. For example, sorting the list will change the order of the list. You may not want this.
By creating a macro to delete the blank rows of a spreadsheet this process will be effortless, and can also be performed by anyone when the macro is made available with a button. In this Excel Macros tutorial we show you how.
To create the delete blank rows in a spreadsheet macro;
- 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 (shown in green) to explain each section of the code.
Sub RemoveRows()
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
Range(“A1”).Select
‘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
ActiveCell.EntireRow.Delete
Else
ActiveCell.Offset(1, 0).Select
End If
Loop
End Sub
If you enjoyed this Excel macros tutorial then check out our online Excel VBA course to fast track your macro writing skills.
Thanks for this. It works great for up to 1826 rows, but I would like it to work for a spreadsheet that is over 15,000 rows. It just stops at 1826 rows for some reason.
Hi Brenda,
I cannot explain this. I just tested it on over 5000 rows quickly and it worked fine. It should work on any number of rows, although obviously many will run slower.
Hello Allan. I believe there is an error in your code. For last row, your formula is counting how many non-blanks instead of how many rows have data. I ran your procedure and it stopped before deleting all the blank rows. I think you have to start from the bottom and use the row number of the last cell that is not blank to properly calculate the last row variable.
Stan
Hi Stan,
Thank you for your message, but to delete all the blanks the formula is correct to count all the non blanks rows. This is when to stop looping so as the rows are removed this will be the finished total.
If we grabbed the last row by starting from the bottom. The loop would continue as it keeps trying to remove rows to get to that number.
great macro…works by deleting stuff if you have nothing in column A but if you want it to work for the whole sheet ….make one nimor change
Change
lastrow = Application.CountA(Range(“A:A”))
To
lastrow = Application.CountA(Range(“A:XFD”))
A good suggestion AK.
Hello Allan,
I have entered code into the window as explained above and i saved the file but it doesn’t effecting my Excel data to remove blank rows . may i know next step to execute macro.
-thank you
You will need a button or keyboard shortcut to execute the macro.
Excellent topic! I certainly agree that empty rows/cells can cause undesirable results. However, I do think the code should be changed:
1. Using lastrow = CountA (…) function will not always give you the last row. For example if there is an entire row between your data that is blank, the count will be off.
For the suggestion of using CountA(“A:XFD”), that also probably will not work as desired for getting the last row. For example, you have 1 row and data in column A,B and C. Using the CountA(“A:XFD”) function, that will make lastrow = 3. (And if there are more cells with data than rows, an error could occur when trying to move to that row).
2. Selecting cells can be slow for large amounts of data – generally it’s not necessary to Select cells. It’s not too bad, just usually not necessary.
The below might not be the best solution, but should be a step closer.
Public Sub RemoveRows()
Dim lastrow As Long
Dim CurrentRow As Long
CurrentRow = 1
lastrow = Range(“A1”).SpecialCells(xlCellTypeLastCell).Row
Do While CurrentRow <= lastrow
If 0 = Application.CountA(Range("A" & CurrentRow).EntireRow) Then
Range("A" & CurrentRow).EntireRow.Delete
lastrow = lastrow – 1
Else
CurrentRow = CurrentRow + 1
End If
Loop
End Sub
Thank you for your comment Mike. Yes there are always multiple ways of achieving a goal with a macro. I write many posts regarding the use of the Cells object and alternatives that are faster than selecting cells in large workbooks, or large macros. However the right one is the one that works and people are comfortable in creating.
On point 1 the CountA is fine because I don’t want to count the blank rows. It is correct in what it is doing because it needs to know when to finish and as the blank rows are being removed, that will be the last row when done, although not when started.
Thanks for the modify code. The original did not work for me. However, your modifications worked on deleting my blank rows.
Many Thanks!!!
I keep getting a ‘Comple error: Syntax error’
I’m new to this so be gentle!
🙂
Syntax error will probably mean that you have made an innocent mistype or misspelling. Double check the code.
Hard for me to tell further without seeing the spreadsheet you are using a code.
Thanks. I copied your code to my Visual Basic Editor. I’m sure it’s me. I’m not used to doing this kind of thing.
Sorry Dell it is hard to help without knowing more. All I can ask is if you check the video and just cross reference the two.
Does it highlight a particular line in the code when showing the error?
It may not be the code, check the spreadsheet is laid out like mine. You may have a different cell range to my example.
I had the same issue. What is highlighted is Sub RemoveRows()