Change VBA code to absolute value

wildturkey

Board Regular
Joined
Feb 21, 2006
Messages
189
Office Version
  1. 365
Platform
  1. Windows
Morning All

I have the following code which I've picked up from the internet and it all works great, however, and with little knowledge, Id like to change the ability to chose the range of rows to an absolute value of rows 28 to 60, so that only blank rows in this range are ignored when printing. Any help appreciated..

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
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
Code:
Sub Print_NonBlank_Rows()
Dim xHideRg As Range, I As Long
Application.ScreenUpdating = False
For I = 28 To 60
    If Application.WorksheetFunction.CountA(Rows(I)) = 0 Then
        If xHideRg Is Nothing Then
            Set xHideRg = Rows(I)
        Else
            Set xHideRg = Union(xHideRg, Rows(I))
        End If
    End If
Next
On Error Resume Next
xHideRg.EntireRow.Hidden = True
ActiveSheet.PrintOut Copies:=1
xHideRg.EntireRow.Hidden = False
Application.ScreenUpdating = True
End Sub
 
Upvote 0
Thanks for the reply, does what I asked for, but I think I jumped the gun assuming the first version worked, both exclude blank rows from printing, but I also need blank rows with formula in them to be skipped - is this an easy tweak please?
 
Upvote 0
Code:
Sub Print_NonBlank_Rows()
Dim xHideRg As Range, I As Long
Application.ScreenUpdating = False
For I = 28 To 60
   If Application.WorksheetFunction.CountBlank(Rows(I)) = Columns.Count Then
        If xHideRg Is Nothing Then
            Set xHideRg = Rows(I)
        Else
            Set xHideRg = Union(xHideRg, Rows(I))
        End If
    End If
Next
On Error Resume Next
xHideRg.EntireRow.Hidden = True
ActiveSheet.PrintOut Copies:=1
xHideRg.EntireRow.Hidden = False
Application.ScreenUpdating = True
End Sub
 
Upvote 0

Forum statistics

Threads
1,223,270
Messages
6,171,102
Members
452,379
Latest member
IainTru

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top