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.
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:
=IFERROR(INDEX($C$5:$C$9,SMALL(IF(NOT(ISBLANK($C$5:$C$9)),ROW($A$1:$A$5),""),ROW(A1)))," ")- Then, hit Enter.
- After that, drag the Fill Handle down from Cell E5 to E7.
- 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.
- 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 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.
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 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.
