Eliminate blank lines in Excel reports

Some reports display blank lines in Excel when exported. This can be inconvenient for printing reports, which may end up printing over too many pages.

The Simple Fix: Sort Your Data

The first way to fix this is to use Excel’s Data > Sort function, which will rearrange your report content and put blank lines at the bottom.

The Macro Fix: To Keep The Display Order Unchanged

For reports where you would prefer not to change to order of items in the list, use the macro below to delete blank lines.

Here’s how to do it:

Open your excel file, and right-click on the worksheet tab. Then, select View Code… from the contextual menu.

viewcode

This will open the Visual Basic Macro Editor window. Simply copy and paste the code below in the window:

Sub efface_A_vide()

Dim l As Long

For l = Cells(65356, 1).End(xlUp).Row To 1 Step -1

If (Cells(l, 1).Value = “” And Cells(l, 2).Value = “” And Cells(l, 3).Value = “” And Cells(l, 4).Value = “” And Cells(l, 5).Value = “” And Cells(l, 6).Value = “” And Cells(l, 7).Value = “” And Cells(l, 8).Value = “” And Cells(l, 9).Value = “”) Then Cells(l, 1).EntireRow.Delete

Next l

End Sub

Finally, run the macro by clicking the Play button in the toolbar.

VB play resized 600

Close the window and you’ll notice all the blank lines are gone!

Leave a comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: