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
 

ravisingh

New Member
Joined
Apr 5, 2019
Messages
46
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
 

HugeJuicyLemmons

New Member
Joined
Apr 9, 2019
Messages
2
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
 

ravisingh

New Member
Joined
Apr 5, 2019
Messages
46
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.
 

Forum statistics

Threads
1,082,601
Messages
5,366,571
Members
400,902
Latest member
fathima

Some videos you may like

This Week's Hot Topics

Top