VBA to skip empty rows before printing

[HugeJuicyLemmons]

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 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]

Try this - I have added a few lines to your code in italics
:

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]

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]

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.