VBA to skip empty rows before printing

HugeJuicyLemmons

New Member
Joined
Apr 9, 2019
Messages
2
Hello,
i am working on a sheet the generates a report in a friendly format and prints it
the way this works is by having a unhidden area that the user generates information for
some of this information is copied over to a hidden area which is the area that is printed when running the macro (range k:p is the report area)
this macro hides the input area unhides the report area, prints, and then rehides the print area and unhides the input area
i am trying to add a way to skip empty rows before my report prints (perhaps hiding them before print and unhiding them after print)



Sub Print_Active_Sheet()
Dim active As Worksheet
Set active = ActiveSheet


ActiveSheet.Unprotect


Columns("A:J").Select
Selection.EntireColumn.Hidden = True

Columns("K:P").Select
Selection.EntireColumn.Hidden = False
ActiveSheet.PrintOut
Selection.EntireColumn.Hidden = True

Columns("A:J").Select
Selection.EntireColumn.Hidden = False

Range("A5").Select




ActiveSheet.Protect DrawingObjects:=True, contents:=True, Scenarios:=True
End Sub

any help would be appreciated!

thnaks
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Try this - I have added a few lines to your code in italics
:
Rich (BB code):
Sub Print_Active_Sheet()
Dim active As Worksheet
Set active = ActiveSheet




ActiveSheet.Unprotect




Columns("A:J").Select
Selection.EntireColumn.Hidden = True


Columns("K:P").Select
Selection.EntireColumn.Hidden = False


Dim PRng As Range
Set PRng = Range(active.PageSetup.PrintArea)
With PRng
    For i = .Rows.Count To 1 Step -1
       If Application.CountA(.Rows(i)) = 0 Then
       .Rows(i).EntireRow.Hidden = True
       End If
    Next
End With


ActiveSheet.PrintOut

PRng.Rows.EntireRow.Hidden = False


Selection.EntireColumn.Hidden = True


Columns("A:J").Select
Selection.EntireColumn.Hidden = False


Range("A5").Select








ActiveSheet.Protect DrawingObjects:=True, contents:=True, Scenarios:=True
End Sub
 
Upvote 0
Hi Ravisingh,
Thank you for the response!

unfortunately the code ran into an error

I enlarged the text where the error occurred and colored it red

any other thoughts?

thanks again!







Sub Print_Active_Sheet()
Dim active As Worksheet
Set active = ActiveSheet








ActiveSheet.Unprotect








Columns("A:J").Select
Selection.EntireColumn.Hidden = True




Columns("K:P").Select
Selection.EntireColumn.Hidden = False




Dim PRng As Range
Set PRng = Range(active.PageSetup.PrintArea)
With PRng
For i = .Rows.Count To 1 Step -1
If Application.CountA(.Rows(i)) = 0 Then
.Rows(i).EntireRow.Hidden = True
End If
Next
End With




ActiveSheet.PrintOut


PRng.Rows.EntireRow.Hidden = False




Selection.EntireColumn.Hidden = True




Columns("A:J").Select
Selection.EntireColumn.Hidden = False




Range("A5").Select


ActiveSheet.Protect DrawingObjects:=True, contents:=True, Scenarios:=True
End Sub
 
Upvote 0
Have you explicitly defined the print range?
For this you need to select the area to be printed and from the ribbon do Page Layout>Print Area>Set Print Area
Pl do this and let me know.
 
Upvote 0

Forum statistics

Threads
1,213,544
Messages
6,114,239
Members
448,555
Latest member
RobertJones1986

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