How to Not Print Empty Cells in Excel (3 Simple Methods)

In this article, we will learn how to not print empty cells in Excel. Sometimes, we import or download a dataset that contains empty cells. Often, we need to ignore these empty cells while printing. So, today, we will show 3 easy methods to not print empty cells in Excel.

Download Practice Book

Download the practice book here.

3 Methods to Not Print Empty Cells in Excel

To explain these methods, we will use a dataset that contains information about the List of Items that the sellers need to sell. There are some empty cells in the list of items.

1. Remove Empty Cells before Printing in Excel

To not print empty cells, we can remove the empty cells before printing. We can follow two sub-methods for this.

1.1 Use ‘Find & Select’ Option

The empty cells can be easily removed by using the ‘Find & Select’ option in Excel. Let’s follow the steps to learn more.

STEPS:

• First of all, select the range of the cells. Here, we have selected Cell B5 to D9.

• Secondly, go to the Home tab and select the ‘Find & Select’ option. A drop-down menu will occur.

• Select Go To Special from the drop-down menu.

• After that, select Blanks from the Go To Special window and then, click OK to proceed.

• After clicking OK, the empty cells will be selected.
• Now, right-click on one of the selected empty cells and select Delete from the context menu.

• Next, select Shift cells up from the Delete message box and click OK.

• Finally, you will see results like the picture below. You can print the sheet without the empty cells. To see print options press Ctrl + P.

1.2 Use IFERROR Function

Another way to get rid of the empty cells is to use a formula. Here, we will use the IFERROR, INDEX, and SMALL Functions together to implement the formula. We will also use the ISBLANK and ROW Functions. Here, we will use the same dataset again.

Let’s pay attention to the steps below.

STEPS:

• In the first place, select Cell E5 and type the formula:

• Then, hit Enter.

• After that, drag the Fill Handle down from Cell E5 to E7.

• Again, select Cell F5 and type the formula:

• Hit Enter to see the result.

• Also, use the Fill Handle to copy the formula from Cell F6 to F8.

• Next, select the range (E5:F8) and press Ctrl + S to copy.

• Finally, select Cell C5 and paste the values only, and then, proceed to print the sheet.

🔎 How Does the Formula Work?

• SMALL(IF(NOT(ISBLANK($C$5:$C$9)), ROW($A$1:$A$5))

Here, this formula will check the empty cells in the corresponding rows and return the non-empty value in Cell E5.

• IFERROR(INDEX($D$5:$D$9,SMALL(IF(NOT(ISBLANK($D$5:$D$9)),ROW($A$1:$A$5),””),ROW(A1))),” “)

This formula will return the non-empty values as a list and execute the next statement if any error occurs.

Note: As we have 5 cells in the first column of the range, so we have written, ROW($A$1:$A$5). If you have 10 cells, we need to use ROW($A$1:$A$10).

Read More: How to Print Selected Cells in Excel (2 Easy Ways)

2. Hide Rows or Columns to Skip Printing Empty Cells

In this method, we will hide rows or columns to not print empty cells in excel. To explain this method, we will use a dataset that contains an empty row like the picture below.

Let’s observe the steps below to know more.

STEPS:

• Firstly, select Row 7.

• Secondly, right-click on Row 7 and select Hide from the context menu.

• After that, you will see results like the below and you can print the sheet without empty cells.

• Once again, you can do the same if you have empty columns.

• In this case, select Column D and right-click on it.
• Then, select Hide from the context menu.

• In the end, you can press Ctrl + P to print the sheet.

Read More: How to Set a Row as Print Titles in Excel (4 Methods)

Similar Readings:

• Print Titles in Excel Is Disabled, How to Enable It?
• How to Print Horizontally in Excel (4 Methods)
• VBA Code for Print Button in Excel (5 Examples)
• How to Print Excel Sheet with Header on Every Page in Excel (3 Methods)
• How to Print Gridlines with Empty Cells in Excel (2 Methods)

3. Apply Excel VBA to Not Print Empty Cells

In the last method, we will apply VBA to hide empty rows. After that, you can print it very easily without empty cells. Here, we will use the previous dataset.

Let’s follow the steps below to learn more.

STEPS:

• To begin with, go to the Developer tab and select Visual Basic. It will open the Visual Basic window.

• After that, select Insert and then Module to open the Module window.
• Then, type the code in the Module window:

Sub Remove_Empty_Rows()
Dim xRng As Range
Dim xCl As Range
Dim xAdrs As String
Dim xUpdt As Boolean
Dim J As Long
On Error Resume Next
xAdrs = Application.ActiveWindow.RangeSelection.Address
Set xRng = Application.InputBox("Select Range", "Microsoft Excel", _
xAdrs, , , , , 8)
Set xRng = Application.Intersect(xRg, ActiveSheet.UsedRange)
If xRng Is Nothing Then Exit Sub
If xRng.Areas.Count > 1 Then
    MsgBox "Not support multiple ranges", , "Microsoft Excel"
    Exit Sub
    End If
    xUpdate = Application.ScreenUpdating
    Application.ScreenUpdating = False
    For J = 1 To xRng.Rows.Count
        xRng.Rows(J).EntireRow.Hidden = (Application.CountA(xRng.Rows(J)) = 0)
    Next
    Application.ScreenUpdating = xUpdt
End Sub

Here, this code will hide the empty rows in a range. This code will ask for the range in the message box first. Then, it will hide the empty rows in that range.

• Now, press Ctrl + S to save the code and then, close the Visual Basic window.
• Again, go to the Developer tab and select Macros to open the Macro window.

• Now, select the desired code from the Macro window and Run it.

• After running the code, a message box will appear. At this time, select the range of your dataset and click OK.

• In the end, you will see results like the picture below.

Related Content: Excel VBA: Print Range of Cells (5 Easy Methods)

Things to Remember

In the above methods, we have shown to remove empty cells, rows, or columns. After following a method, you need to press Ctrl + P to see the preview and print options.

Conclusion

We have demonstrated 3 easy methods to Not Print Empty Cells in Excel. We have used different examples and also discussed the method with VBA. I hope these methods will help you to perform your tasks easily. Furthermore, we have also added the practice book at the beginning of the article. You can download it to learn more. Last of all, if you have any suggestions or queries, feel free to ask in the comment section below.

Related Articles

• How to Print Excel Sheet with Table (4 Methods + Tricks)
• Excel VBA: How to Set Print Area Dynamically (7 Ways)
• How to Print Excel Sheet in Full Page (7 Ways)
• Print Excel Sheet in A4 Size (4 Ways)
• How to Keep Header in Excel When Printing (3 Ways)