Often we get to copy paste data from several excel sheets and it turns out that there are blank lines which needs to be deleted. Scrolling over the entire spreadsheet and manually removing each blank line is a tedious job. Here is a small excel macro to remove all the blank lines from selected data in excel sheet just in few seconds.
First step is to create a Macro. For creating a macro, click on Tools in menu bar, select Macro and then Macros in sub menu as shown in Figure 1 below.
Clicking on Macros in Figure 1 will open the below window. Type the name of Macro as “DeleteBlankRows” or a name of your choice and click on Create button.
Clicking on Create button will launch the Visual Basic editor as below.
Copy below code and paste it in the editor.
Dim i As Long
Application.ScreenUpdating = False
Application.StatusBar = "Deleting the blank lines..."
Application.DisplayAlerts = True
For i = Selection.Rows.Count To 1 Step -1
Application.StatusBar = "Deleting blank lines " & Format((1 - (i / Selection.Rows.Count)), "0 %") & "..."
If WorksheetFunction.CountA(Selection.Rows(i)) = 0 Then
Application.StatusBar = False
Application.ScreenUpdating = True
Save this Macro by closing the editor.
Select the range of data and run the Macro by clicking on Tools->Macro->Macros. Select the Macro “DeleteBlankRows” and click on “Run” button. All blank lines will be removed.
Note: This macro will remove blank rows only if the entire row does not contain any data.
To customize this Macro further, follow below steps.
- Go to Tools->Macro->Macros. Select the Macro “Compare” and click on “Edit” button.
- This will launch the Visual Basic Editor.
- Make the required changes.
- Save the Macro.
Hope you find this article useful, your suggestions and feedback are always welcome.