PrintArea Problems (Excel 2000)

Topic

New Reply

Hi all. Hope someone can help me with this one as I have very little hair left now…

I need to set a dynamic print area depending on how many rows have data in them. I’m currently using this line of code

ActiveSheet.PageSetup.PrintArea = Range(Cells(1, 1), Cells(count, 13))

where ‘count’ is calculated earlier and is the number of rows that need to be printed. I can’t see where I’m going wrong. Any help would be much appreciated. TIA

Reply | Quote

Replies

Could you show us the line that sets the value of count?

Reply | Quote

Create a dynamic range name, and then set the range name as the print area or in the code.

eg, =OFFSET$A$1,0,0,COUNTA($A:$A),10) calling it PrintRange

Reply | Quote

How about:

ActiveSheet.PageSetup.PrintArea = Range(Cells(1, 1), Cells(count, 13)).address

Steve

Reply | Quote

Try this after you have created the dynamic range I mentioned in post 478248

Option Explicit
Sub SetDynamicPA()
Dim PA As Range

    Application.ScreenUpdating = False
    Application.Goto Reference:="DR" '(The name of the Dynamic Range Name)
    Set PA = Selection
    ActiveSheet.PageSetup.PrintArea = PA
    PA.Cells(1).Select
    Application.ScreenUpdating = True
    
End Sub

Cheers

Reply | Quote

Thanks one and all. I’ve just got into work so haven’t had chance to try any of your suggestions yet. When I do I’ll let you know how I got on.

Reply | Quote

Hi Lyra,
Have you tried creating a dynamic range called ‘Sheetname’!Print_Area (replace with the name of your worksheet as appropriate). This should automatically update the print range as long as you don’t press the clear print area button. (at least it works for me in Excel2002)

Reply | Quote

This is great Rory. In my last two posts, I created the same thing MANUALLY, using a Dynamic Range Name and a macro. Yours does it ALL automatically and in one simple step!!!

Cool Tip..Lyra should enjoy this one!!!

Reply | Quote

I recommend creating another identical one with a “similar name” (perhaps “Print_AreaBckUp”).

The Print_Area name is one excel uses: clearing print area or changing it in page setup will affect this name. Having a separate one with a different name will keep a “backup”. If the print_area gets changed, just go to insert – name – define, select “Print_AreaBckUp” and edit the name to remove the “BckUp” and you will “restore” the dynamic range without having to regenerate the OFFSET formula

Steve

Reply | Quote

Reply | Quote

[indent]

---

just go to insert – name – define, select “Print_AreaBckUp” and edit the name to remove the “BckUp” and you will “restore” the dynamic range without having to regenerate the OFFSET formula

---

[/indent]Or alternatively, change the Sheet1!PrintArea to point to the “backup” name directly:
=Print_AreaBckUp

Reply | Quote

A good alternative. It all depends on whether you want to deal with names or the page setup.

Either method can lose the printarea in a variety of ways, but both have backup names so changing is not much a problem…

Steve

Reply | Quote