Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or

How to print only rows /cells with data in Excel?

When printing a range or a worksheet in Excel, some blank rows or blank cells are also printed out and take up spaces in papers. This article is talking about how to ignore all blank rows/cells and only print rows/cells with data in Excel.

Print only rows with data by VBA in Excel

Print only rows with data by removing blank rows in Excel

Print only cells with data by copying cells with data to a new sheet in Excel

Batch print multiple nonblank rows with ignoring empty rows in Excel

Kutools for Excel's Print Multiple Selections Wizard can help you not only print multiple selections from the same worksheet easily, but also print multiple ranges from many worksheets easily. Full Feature Free Trial 60-day!

ad print multiple selections 2



This method will introduce a VBA to quickly print rows with data only in a specified range in Excel. Please do as follows:

1. Press the Alt + F11 keys simultaneously to open the Microsoft Visual Basic for Applications window.

2. Click Insert > Module, and then copy and paste the following VBA code into the new opening Module window.

VBA: Print only rows with data in Excel

Sub PrintSummary()
Dim xRg As Range, xRg1 As Range, xCell As Range
Dim xSht As Worksheet
Dim xTxt As String
On Error Resume Next
xTxt = ActiveWindow.RangeSelection.Address
Set xRg = Application.InputBox("Please select the range to print:", "Kutools for Excel", xTxt, , , , , 8)
Set xRg = Application.Intersect(xRg, ActiveSheet.UsedRange)
If xRg Is Nothing Then Exit Sub
If xRg.Areas.Count > 1 Then
MsgBox "You can't select multiple selections", , "Kutools for Excel"
Exit Sub
End If
Application.DisplayAlerts = False
Application.ScreenUpdating = False
Set xSht = Worksheets.Add(After:=Sheets(Sheets.Count))
xRg.Copy xSht.Range("A1")
Set xRg1 = xSht.Range("A1").Resize(xRg.Rows.Count, xRg.Columns.Count)
For Each xCell In xRg1.Columns(1).Cells
If Application.WorksheetFunction.CountA(xCell.EntireRow) = 0 Then
xCell.EntireRow.Hidden = True
End If
Next
xSht.PrintOut Copies:=1, Collate:=True, IgnorePrintAreas:=False
xSht.Delete
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

3. Press the F5 key or click the Run button to run this VBA.

4. In the opening Kutools for Excel dialog box, specify the range that you want to ignore blank rows and only print rows with data, and click the OK button.

Then the specified range is printed immediately with ignoring all blank rows in it.

Note: This VBA does not support batch print multiple selections with ignoring blank rows.


This method introduce Kutools for Excel’s Delete Blank Rows utility to delete all blank cells in active worksheet or selected range, and then print those rows with data only in Excel. Please do as follows:

Kutools for Excel - Combines more than 300 Advanced Functions and Tools for Microsoft Excel

1. Go to the worksheet you will print rows with data only, and click Kutools > Delete > Delete Blank Rows > In Active Sheet. See screenshot:

Note: For printing rows with data only in a specified range, please select this range firstly, and then click Kutools > Delete > Delete Blank Rows > In Selected Range.

2. A Kutools for Excel dialog box will pop up and show you how many blank rows have been deleted. Click the OK button to close it.

And now you will see all blank rows are deleted from the active worksheet/selected range. See screenshot:

3. Click the File (or Office Button) > Print to print only rows with data in active worksheet or selected range.

Kutools for Excel - Includes more than 300 handy Excel tools. Full feature free trial 60-day, no credit card required! Get it now!


Sometimes, you may want to print cells with data only with ignoring all blank cells in Excel. This method will introduce Kutools for Excel’s Select Nonblank Cells utility and Print Multiple Selections Wizard to only print cells with data in specified range in Excel.

Kutools for Excel - Combines more than 300 Advanced Functions and Tools for Microsoft Excel

1. Select the range where you will only print the cells with data, and click Kutools > Select > Select Nonblank Cells. See screenshot:

2. A Kutools for Excel dialog box comes out and show you how many cells with data have been selected. Click the OK button to close it.

3. Keep selecting these nonblank cells, and click Enterprise > Printing > Print Multiple Selections Wizard. See screenshot:

4. In the opening Print Multiple Selections Wizard dialog box, you will see all selected nonblank cells are listing in the Ranges to print list, please click the Finish button.

5. Now all selected nonblank cells are copied and pasted as linked pictures in a new worksheet. Please click File (or Office button) > Print to print these cells with data.

Kutools for Excel - Includes more than 300 handy Excel tools. Full feature free trial 60-day, no credit card required! Get it now!


In this Video, the Kutools tab and the Kutools Plus tab are added by Kutools for Excel. If need it, please click here to have a 60-day free trial without limitation!



Recommended Productivity Tools

Ribbon of Excel (with Kutools for Excel installed)

300+ Advanced Features Increase Your Productivity by 71%, and Help You To Stand Out From Crowd!

Would you like to complete your daily work quickly and perfectly? Kutools For Excel brings 300+ cool and powerful advanced features (Combine workbooks, sum by color, split cell contents, convert date, and so on...) for 1500+ work scenarios, helps you solve 82% Excel problems.

  •  Deal with all complicated tasks in seconds, help to enhance your work ability, get success from the fierce competition, and never worry about being fired.
  •  Save a lot of work time, leave much time for you to love and care the family and enjoy a comfortable life now.
  •  Reduce thousands of keyboard and mouse clicks every day, relieve your tired eyes and hands, and give you a healthy body.
  •  Become an Excel expert in 3 minutes, and get admiring glance from your colleagues or friends.
  •  No longer need to remember any painful formulas and VBA codes, have a relaxing and pleasant mind, give you a thrill you've never had before.
  •  Spend only $39, but worth than $4000 training of others. Being used by 110,000 elites and 300+ well-known companies.
  •  60-day unlimited free trial. 60-day money back guarantee. Free upgrade and support for 2 years. Buy once, use forever.
  •  Change the way you work now, and give you a better life immediately!

Office Tab Brings Efficient And Handy Tabs to Office (include Excel), Just Like Chrome, Firefox, And New IE

  • Increases your productivity by 50% when viewing and editing multiple documents.
  • Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
  • Open and create documents in new tabs of same window, rather than in new windows.
  • Help you work faster and easily stand out from the crowd! One second to switch between dozens of open documents!
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.

Be the first to comment.