PDA

View Full Version : VBA to adjust print area based on UsedRange



agnesz
05-01-2009, 10:03 AM
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
05-01-2009, 07:21 PM
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 SubWhat 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
05-04-2009, 06:46 AM
Actually the columns are added through vba. How can I adjust this code to work with that? Thanks so much macropod.

macropod
05-04-2009, 02:47 PM
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.