• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 176
  • Last Modified:

VBA Code to shift print area based on last row of data

I have a spreadsheet that currently has the print area set as c3:0133.  I would like to have the print area reset (I guess with a Before_Print Command)  based on the last row of data at the time of printing.  This spreadsheet is a case expense log where users log their expenses in rows...but the amount of entries/rows will vary every time.  For example, if a user enters rows of data down to row 50 then before they print the print area would change to c3:c50
0
RRUTLAND5
Asked:
RRUTLAND5
  • 2
1 Solution
 
Dave BrettCommented:
something like this in the ThisWorkbook module (to print c3:o50 in you example)
Cheers
Dave

Private Sub Workbook_BeforePrint(Cancel As Boolean)
    Dim ws As Worksheet, rng1
    Set ws = Sheets(1)
    If ws.Name <> ActiveSheet.Name Then Exit Sub
    Set rng1 = ws.Range(ws.[c3], ws.Cells(Rows.Count, "o").End(xlUp))
    ws.PageSetup.PrintArea = rng1.Address
End Sub

Open in new window

0
 
Dave BrettCommented:
And I should have noted that the code is set to run on the first sheet, ie
Set ws = Sheets(1)

Cheers
Dave
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now