Excel VBA set print area with user input

Several hours trying to get this to work. Am defeated.

I have a budget worksheet with Column A containing Budget Categories and Row 8 containing MM/YYYY.

I have Column A and Row 8 as 'fixed' so that they print on each page.

A user might want only 4 (consecutive) months on a report.

I would like to have a macro that Says: "Select the Beginning Month of your Report". User selects cell that has Mar-2015 . Then, "Select the Ending Month of your Report". User selects Jun-2015 .

The number of rows to print is hard-coded at 80, so (with Column A containing Cats and months beginning with Jan-2015 in Column B, the Print Area would be D8:H80).

I've tried numerous ways of 'assembly-ing' the Print Area based on both a Column number extracted from the user input for beginning and ending -- and I've been able to extract the Address for the Begin Date cell the user selected (and End Date) -- but I don't seem to understand how and when to use Address in the print area.

Here's one of several I've tried -- and there is much amiss with this one....

Please Login or Register  to view this content.

Thank you.

Hi swtrader,

I understand your frustration. You were very close to solving the problem. The secret is the understanding of the difference between variables and objects. Until the difference is understood, there will probably be a lot of head scratching and bad words.

The Page Setup Print Area expects the address as a String, that is why you would need the .Address if you used a Range object (MyPrntRng.Address).

Another possible solution, is to declare the Print Range to contain all your data, and to Hide the Columns that you don't want printed.

I will answer attempt to your question with the following 4 examples:

The first example is a corrected copy of your original code, with the changes in red.

Please Login or Register  to view this content.

The second example only needs one input, the entire Print Range

Please Login or Register  to view this content.

The Third Example is similar to the first example, but restricts date selection o Dates in Column 'A'. Then it is assumed the print area extends to Column 'G' and creates a 'Final Print Area'.

Please Login or Register  to view this content.

Finally, a simple example of how columns could be hidden:

Please Login or Register  to view this content.

I hope this helps.

Lewis