How to Clean Up this easy Range Selection Syntax

**clemsoncooz** · 02-09-2012, 11:42 AM

I know that this is a simple request - but I am having some trouble as I am still new to this.

Isn't there an easier way to perform this action?

    Range("A1", Range("B500").End(xlUp)).Select
    Selection.Resize(Selection.Rows.Count, Selection.Columns.Count + 1).Select
    ActiveSheet.PageSetup.PrintArea = Selection.Address

Thanks and sorry in advance.

**clemsoncooz** · 02-09-2012, 02:33 PM

Should I just stay with it then. It doesnt pose any problems....just didnt know if I should clean it up since most of the advice I receive is "don't use select"

**maczaq** · 02-09-2012, 02:54 PM

what about this

ActiveSheet.PageSetup.PrintArea = Range("A1", Range("B500").End(xlUp).Offset(, 1)).Address

**clemsoncooz** · 02-09-2012, 03:08 PM

yea, yea thats what I was talking about. That does solve my question....but now that I run that code, I realize my question wasn't asked correctly because while your code is correct, it doesnt work correctly on my sheet because of the layout.

The resulting print area is (2) rows off and here is why.

I chose Column B because B31 to G33 is a merged cell. I am offsetting 1 column because I have data in G, but NOT H, and I need my print aread to extend to H. So when you offset 1 column from the merged cell of B31:G33, the result is H31. NOT H33, therefore it is off by (2) rows.

Know how to fix?

**maczaq** · 02-09-2012, 03:18 PM

so try to use

ActiveSheet.PageSetup.PrintArea = Range("A1", Range("B500").End(xlUp).Offset(2, 1)).Address

or attach your workbook with dummy data to show me your sheet structure

**clemsoncooz** · 02-09-2012, 03:26 PM

Got it.

Thank you so much for your help!

Thread: How to Clean Up this easy Range Selection Syntax

Thread Tools

Display

How to Clean Up this easy Range Selection Syntax

Re: How to Clean Up this easy Range Selection Syntax

Re: How to Clean Up this easy Range Selection Syntax

Re: How to Clean Up this easy Range Selection Syntax

Re: How to Clean Up this easy Range Selection Syntax

Re: How to Clean Up this easy Range Selection Syntax

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions