Back on the Excel train again.
Is there a relatively simple way to have a bunch of rows formatted, but not print them if they are empty?
Currently if they are empty, but formatted (merged and what not), they will print.
I found a quick simple thing that allowed the user to select a range to hide before printing, but (to me) it is not very intuitive.
Anyone have a better, more user friendly method?
Is there a relatively simple way to have a bunch of rows formatted, but not print them if they are empty?
Currently if they are empty, but formatted (merged and what not), they will print.
I found a quick simple thing that allowed the user to select a range to hide before printing, but (to me) it is not very intuitive.
Code:
Sub HideBlankRows()
Dim xRg As Range
Dim xCell As Range
Dim xAddress As String
Dim xUpdate As Boolean
Dim I As Long
On Error Resume Next
xAddress = Application.ActiveWindow.RangeSelection.Address
Set xRg = Application.InputBox("Select range to NOT print.", "Print Transmittal", xAddress, , , , , 8)
Set xRg = Application.Intersect(xRg, ActiveSheet.UsedRange)
If xRg Is Nothing Then Exit Sub
If xRg.Areas.Count > 1 Then
MsgBox "Don't support multiple ranges", , "Kutools for Excel"
Exit Sub
End If
xUpdate = Application.ScreenUpdating
Application.ScreenUpdating = False
For I = 1 To xRg.Rows.Count
xRg.Rows(I).EntireRow.Hidden = (Application.CountA(xRg.Rows(I)) = 0)
Next
Application.ScreenUpdating = xUpdate
Call PrintToPDF
On Error Resume Next
xAddress = Application.ActiveWindow.RangeSelection.Address
If xRg Is Nothing Then Exit Sub
xUpdate = Application.ScreenUpdating
Application.ScreenUpdating = False
xRg.EntireRow.Hidden = False
Application.ScreenUpdating = xUpdate
End Sub
Anyone have a better, more user friendly method?