How to create macro to set print area for flexible range

[chriscalc]

Hi! I get a number of Excel spreadsheets on a daily basis that are poorly formatted for printing. I want to create a macro to set the print area to print them.
All Sheets start in cell A1.
Ideally it would be a macro that would set the print area to a range A1:last cell with data in it. Alternatively it would set the print area to whatever range I have highlighted.
This should be simple but I have tried record macro and help but I either end up with a macro that selects a specific range or with one that only selects one cell.
Any help is appreciated.
Chris

 

[kjv1611]

First, Macro/VBA questions are to be asked here: forum707

So if this doesn't work well enough, then repost over in that forum, and restate your question. You could reference this thread in the new thread to point out where you first asked the question, but it really needs to be asked in the other forum.

--

"If to err is human, then I must be some kind of human!" -Me

 

[kjv1611]

Yeah, I just realized I did it here too. I guess I shoulda been drinking some coffee this morning!

Please ignore my forum mention.... I started in one forum, and ended up here, and just forgot I swapped forums. I keep trying to click the EDIT button, but alas, it seems to be amiss.

--

"If to err is human, then I must be some kind of human!" -Me

 

[chriscalc]

This is one of the macros I tried: I know this should be as basic as it gets but I am a basic user so I do not know how to write VBA. Again, I would like the range to be either A1: to the last cell with data or just the area that I have highlighted.

Sub Set_print_area()
'
' Set_print_area Macro
'
' Keyboard Shortcut: Ctrl+Shift+H
'
Range("A1:J14").Select
ActiveSheet.PageSetup.PrintArea = "$A$1:$J$14"
End Sub

Here is the other one I tried based on the help in the troubleshooter. It only highlights one cell...

Sub Set_print_area()
'
' Set_print_area Macro
'
' Keyboard Shortcut: Ctrl+Shift+H
'
Worksheets("Sheet1").Activate
ActiveSheet.PageSetup.PrintArea = _
ActiveCell.CurrentRegion.Address
End Sub

 

[Loomah]

hi chris
a couple of things you could consider to identify your range:
1 - if the data is contiguous then you can use

range("a1").currentregion

to identify your range
2 - if you have a look at the faq area for this forum there are at least 2 faqs (one from xlbo, the other from me) highlighting different ways to identify the true las cell used. the reason i stree true is that neither usedrange nor specialcells(xlcelltypelastcell) are guaranteed reliable. once you have your last cell address and you know your first cell you can construct the range to set as print area.

i've deliberately stayed awy from the actual page setup code as, if you don't know it, you can easily do what i do and record that bit!!

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[kjv1611]

Well, those SHOULD work, assuming you selected a cell within a block of cells with text in them, I would think.

However, if You're wanting to select the cells yourself, and then run the macro, then you could try this code:

Private Sub Set_print_area()
     Dim wb as Workbook
     Dim ws as Worksheet

     Set wb = ActiveWorkbook
     Set ws = wb.ActiveSheet
   [green]'Clear the current print area first - not sure if you actually NEED to do this[/green]
     ActiveSheet.PageSetup.PrintArea = vbNullString
   [green]'Set the print area to the current selection[/green]
     ws.PageSetup.PrintArea = ws.Selection

     Set ws = Nothing
     Set wb = Nothing
End Sub

I haven't tested this code, but I think it'll work.

Also, here's a reference I found on the topic, so you can see if it gives you any help:

http://www.vbaexpress.com/kb/getarticle.php?kb_id=125

And just the other day, someone posted a good keyboard shortcut for selecting the current region that seemed to work better than anything else that I know of:
<Ctrl> + <*> or <Ctrl> + <Shift> + <8>
Note that <Shift> + <8> will give you the asterisk character, so it's really the same thing, but using different keys to get the same result. You could try using that keyboard combination while you are recording a new macro, and see if it gives you better results.

--

"If to err is human, then I must be some kind of human!" -Me

 

[chriscalc]

Thanks for the responses! Loomah: I tried to figure out the code and the FAQ but cannot get it to work because I think they are too advanced. Embarassing but true. kjv1611: I have tried to integrate the code you wrote into my macro but cannot get it to start using <Ctrl> <Shift> H. Here it is, I would appreciate help in fixing it. I would most likely need it in a format I could literally paste into the editor. Thanks!

Sub Set_print_area()
'
' Set_print_area Macro
'
' Keyboard Shortcut: Ctrl+Shift+H
'
Dim wb As Workbook
Dim ws As Worksheet

Set wb = ActiveWorkbook
Set ws = wb.ActiveSheet
ActiveSheet.PageSetup.PrintArea = vbNullString
ws.PageSetup.PrintArea = ws.Selection

Set ws = Nothing
Set wb = Nothing

End Sub

 

[Loomah]

that would have been me!!

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[SkipVought]

Here's your code cleaned up...

Sub Set_print_area()
'
' Set_print_area Macro
'
' Keyboard Shortcut: Ctrl+Shift+H
'
     With ActiveSheet
        .PageSetup.PrintArea = .Range("A1").CurrentRegion
     End With
End Sub

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[kjv1611]

but cannot get it to start using <Ctrl> <Shift> H.

That isn't done directly in the code. You can control that under Tools-Macros, and find and edit the macro there... make sure you assign whatever key combination you want to use.

Or if you'd prefer a button, prior to 2007, you can easily add a new button to a toolbar. I'm not sure right away of how to do that in 2007.. I guess you could add it to the Quick Access Toolbar, but I've yet to try.... come to think of it, it's something I need to be looking into fairly soon, I suppose.

--

"If to err is human, then I must be some kind of human!" -Me

 

[chriscalc]

kjv1611: I tried pasting the code you wrote and running it.

I get an error message at ws.Selection saying "Compile error: Method or data member not found".

Suggestions would be appreciated.

Chris

 

[chriscalc]

Skipvought: Thanks for the code. I have put it into the editor but I get an error message "Run-time error '1004': The text you entered is not a valid reference or defined name. The debugger highlights the line

.PageSetup.PrintArea = .Range("A1").CurrentRegion

Thoughts appreciated.

kjv1611 I will try yours again now.

Thanks for the help!

 

[kjv1611]

Hmm, instead of:

ws.Selection

Try

Selection

Or

Excel.Selection

Or

Application.Selection

--

"If to err is human, then I must be some kind of human!" -Me

 

[kjv1611]

Also, to play on this usage, as mentioned above:

.Range("A1").CurrentRegion

I think you'd be better off doing it more like this:

Private Sub Set_print_area()
     Dim wb as Workbook
     Dim ws as Worksheet

     Set wb = ActiveWorkbook
     Set ws = wb.ActiveSheet
   [GREEN]'Clear the current print area first - not sure if you actually NEED to do this[/GREEN]
     ws.PageSetup.PrintArea = vbNullString
   [GREEN]'Set the print area to the current selection
     ws.PageSetup.PrintArea = ActiveCell.CurrentRegion[/GREEN]

     Set ws = Nothing
     Set wb = Nothing
End Sub

I believe then all you'd have to do is select a cell in the region you want to print (whether it starts at cell A1 or whether it starts at cell Z400), so long as you've selected a cell in that range, it should work. I believe it's the same as using the <Ctrl> + <*> combination for the selection.

The clearing of the Print Area may not be necessary, but I thought it'd be good practice to do so, just in case.

--

"If to err is human, then I must be some kind of human!" -Me

 

[kjv1611]

Well, for easier reading, I'll correct my mistake above (coloring of text)

Private Sub Set_print_area()
     Dim wb as Workbook
     Dim ws as Worksheet

     Set wb = ActiveWorkbook
     Set ws = wb.ActiveSheet
   [GREEN]'Clear the current print area first - not sure if you actually NEED to do this[/GREEN]
     ws.PageSetup.PrintArea = vbNullString
   [GREEN]'Set the print area to the current selection[/GREEN]
     ws.PageSetup.PrintArea = ActiveCell.CurrentRegion

     Set ws = Nothing
     Set wb = Nothing
End Sub

--

"If to err is human, then I must be some kind of human!" -Me

 

[chriscalc]

Need to get on a conference call for 45 mins but will try shortly. Thanks!

 

[Loomah]

just a random thought but isn't printarea is a string??

so to set it you'd surely need

ws.pagesetup.printarea = range("a1").currentregion.address

perhaps?

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[SkipVought]

sorry...

Sub Set_print_area()
'
' Set_print_area Macro
'
' Keyboard Shortcut: Ctrl+Shift+H
'
     With ActiveSheet
        .PageSetup.PrintArea = .Range("A1").CurrentRegion[b].address[/b]
     End With
End Sub

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}