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

Get FREE Advanced Excel Exercises with Solutions!

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.


How to Not Print Empty Cells in Excel: 3 Simple Methods

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.

Remove Empty Cells before Printing in Excel


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.

Remove Empty Cells before Printing in Excel

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

Remove Empty Cells before Printing in Excel

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

Remove Empty Cells before Printing in Excel

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

Remove Empty Cells before Printing in Excel

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

Remove Empty Cells before Printing in Excel

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

Remove Empty Cells before Printing in Excel

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

Remove Empty Cells before Printing in Excel


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:
=IFERROR(INDEX($C$5:$C$9,SMALL(IF(NOT(ISBLANK($C$5:$C$9)),ROW($A$1:$A$5),""),ROW(A1)))," ")
  • Then, hit Enter.

Remove Empty Cells before Printing in Excel

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

Remove Empty Cells before Printing in Excel

  • Again, select Cell F5 and type the formula:
=IFERROR(INDEX($D$5:$D$9,SMALL(IF(NOT(ISBLANK($D$5:$D$9)),ROW($A$1:$A$5),""),ROW(A1)))," ")
  • Hit Enter to see the result.

Remove Empty Cells before Printing in Excel

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

Remove Empty Cells before Printing in Excel

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

Remove Empty Cells before Printing in Excel

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

Remove Empty Cells before Printing in Excel

🔎 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 Excel Sheet with Table


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.

Hide Rows or Columns to Skip Printing Empty Cells

Let’s observe the steps below to know more.

STEPS:

  • Firstly, select Row 7.

Hide Rows or Columns to Skip Printing Empty Cells

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

Hide Rows or Columns to Skip Printing Empty Cells

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

Hide Rows or Columns to Skip Printing Empty Cells

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

Hide Rows or Columns to Skip Printing Empty Cells

  • 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 Print Landscape in Excel


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.

Read More: How to Print Horizontally in Excel


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.


Download Practice Book

Download the practice book here.


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

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Mursalin Ibne Salehin
Mursalin Ibne Salehin

Hi there! This is Mursalin. I am currently working as a Team Leader at ExcelDemy. I am always motivated to gather knowledge from different sources and find solutions to problems in easier ways. I manage and help the writers to develop quality content in Excel and VBA-related topics.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo