How to stop blank rows when printing worksheet?
If there are multiple blank rows in your worksheet, normally, when you print the sheet, the blank rows will be printed as well. To print the worksheet ignoring the blank rows can save papers for us, in this case, how could you stop blank rows when printing a worksheet?
With the following VBA code, you can print the worksheet without blank rows, please do as this:
1. Hold down the ALT + F11 keys to open the Microsoft Visual Basic for Applications window.
2. Click Insert > Module, and paste the following code in the Module Window.
VBA code: Stop blank rows when printing:
Sub Print_NonBlank_Rows() 'Updateby Extendoffice 20160704 Dim xStr As String Dim xRg As Range Dim xHideRg As Range Dim xTxt As String Dim I As Long On Error Resume Next If ActiveWindow.RangeSelection.Count > 1 Then xTxt = ActiveWindow.RangeSelection.AddressLocal Else xTxt = ActiveSheet.UsedRange.AddressLocal End If Set xRg = Application.InputBox("please select the data range:", "Kutools for Excel", xTxt, , , , , 8) If xRg Is Nothing Then Exit Sub Application.ScreenUpdating = False For I = 1 To xRg.Rows.Count If Application.WorksheetFunction.CountA(xRg.Rows(I)) = 0 Then If xHideRg Is Nothing Then Set xHideRg = xRg.Rows(I) Else Set xHideRg = Union(xHideRg, xRg.Rows(I)) End If End If Next xHideRg.EntireRow.Hidden = True ActiveSheet.PrintOut Copies:=1 xHideRg.EntireRow.Hidden = False Application.ScreenUpdating = True End Sub
3. Then press F5 key to run this code, and a prompt box will pop out to remind you select the data range that you want to print, see screenshot:
4. And then click OK, and your selected range has been printed at once without the blank rows.
If the blank rows are useless for you, you can delete all the blank rows at first, and then print the worksheet as you need. With Kutools for Excel’s Delete Blank Rows feature, you can quickly delete the blank rows in a selection, worksheet or workbook as you need.
|Kutools for Excel : with more than 300 handy Excel add-ins, free to try with no limitation in 60 days.|
After installing Kutools for Excel, please do as follows:
1. Select the data range that you want to delete the blank rows, and then click Kutools > Delete > Delete Blank Rows > In Selected Range, see screenshot:
2. And a prompt box will pop out to remind you how many blank rows deleted in a selection.
3. After deleting the blank rows, then you can print the worksheet.