To delete blank rows in-between used range of an excel worksheet
Tips to delete blank rows:
If data is small and number of blank rows is less, it’s easier to delete the same
However, in a scenario where data is huge and number of blank rows is in hundreds or thousands, following methods may be used to delete the unwanted blank rows:
(Structured Data – if some rows are blank entirely in between given data)
If your data is structured and it is possible to select all blank rows from any column, you may use any of the following 2 tricks:
1) Select entire data and apply auto filter (from ribbon or by pressing “Ctrl+Shift+L” together). Now, from any drop-down button, select “blank cells” only and execute “Delete Sheet Rows” command (can be accessed by right click button on mouse or from the ribbon). Excel will delete all blank rows in one go.
2) If data is structured, other option is to select any column and press “F5” or “Ctrl+G”. Excel will open a new window – “Go To”. From this new window, click on radio button “Special”. This time Excel will show options under “Go To Special”. Now select the option of “Blanks” in it. Excel will select all blank cells from the selected column. Now, execute “Delete Sheet Rows” command (can be accessed by right click button on mouse or from the ribbon) to delete all blank rows.
Non-Structured Data – if it is not possible to select entire blank rows from any column)
In such situation macro will come to rescue
• Find out last row number of used range
• Move upward from last row to row one
• Check whether a row is blank or not using built-in worksheet function “COUNTA”, it will return “0” if a row is blank
• If the row is blank delete it
• Move to next row
Sub DeleteBlankRows() Dim LastRow As Long Dim i As Long LastRow = ActiveSheet.UsedRange. SpecialCells(xlCellTypeLastCell).Row For i = LastRow To 1 Step -1 If Application.CountA(Rows(i)) = 0 Then Rows(i).Delete End If Next i End Sub
Value of last row of used range assigned to variable “LastRow”.
We have used other variable “i” to start a “FOR LOOP” from “LastRow” to row one, then we check each row, whether the same is blank or not and delete it, if it’s blank.
How to use above code in your workbook:
If you have not used VBA code earlier, you must be thinking:
- Where to paste the code?”
- How to run the macro to delete the blank rows?
To use the above code, just follow the below mentioned simple steps:
1. Copy the above code and open the workbook, where you want to use this code
2. In the workbook press “Alt+F11”, Excel will open a new window “Microsoft Visual Basic for Application”
3. In the new window opened, click “Insert” tab and select the “Module”, and Excel will insert a new “Module1” in the workbook
4. Now, you will see that the mouse cursor is blinking in the newly opened white window, “Module1”. Here, you can paste the code by simply pressing the key board shortcuts “Ctrl+V” or right click on the mouse and select “Paste”. You can also paste the code by clicking “Edit” tab and selecting “Paste”
To run the code, select the “VIEW” tab of you workbook. Here in extreme right group “Macros”, select “View Macros”, with this Excel will open a new window “Macro”. Here select the macro “DeleteBlankRows” and press the “Run” to execute the macro.