Macro To Delete Blank Rows In Excel Spreadsheet

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.

Creating Macro

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.

Figure 1

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.

Figure 2

Clicking on Create button will launch the Visual Basic editor as below.

Figure 3

Copy below code and paste it in the editor.

Save this Macro by closing the editor.

Running Macro

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.

Editing Macro

  • 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.

  1. The reason we turn off calculation is in case the range in which we are deleting rows contains lots of formulas, if it does Excel may need to recalculate each time a row is deleted, slowing down the macro.

Leave a Reply

Your email address will not be published. Required fields are marked *


You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">