VBA to adjust print area based on UsedRange

[agnesz]

What vba do I need to add to existing code to ensure that the print area only includes the used range. I have a dynamic report that will be run weekly and columns/row may get added or deleted on a regular basis. I'd like the print range to automatically update prior to printing to only include the currently used range.

Any help would be much appreciated.
Thank you!

[macropod]

Hi agnesz,

You could achieve this by adding the following code to the workbook's 'This Workbook' module:

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
ActiveSheet.UsedRange
End Sub

What this code does is to reset Excel's view of the used range for the current worksheet any time the selection is changed or the <Enter> key is pressed. Unless your columns/rows are being added or deleted via vba, that should do the trick.

[agnesz]

Actually the columns are added through vba. How can I adjust this code to work with that? Thanks so much macropod.

[macropod]

Hi agnesz,

Simply add 'ActiveSheet.UsedRange' (or just '.UsedRange' if you're already working with a defined sheet name inside an 'With' statement) to your existing code.