Setting dynamic print area 2

[shawnmandel]

Is there any way to set the print area to be dynamic? Is there a way to detect the last row and/or column so that the print area can be automatically adjusted accordingly?

Thanks,
Shawn

 

[DreamerZ]

You could use the Find to locate the last row:

Set find_Last = sheet_Name.Cells.Find(what:="", _
    after:=sheet_Name.Range("A1"), searchorder:=xlByColumns)

Change the xlbyColumns to xlByRows to search for the last of one or the other.

I hear talk of a lastrow command, but I can't find information on it.

Hope that helps.


DreamerZ
simplesolutions@prodigy.net
[ignore]

http://www.geocities.com/simplesolutions@prodigy.net

[/ignore]

 

[shawnmandel]

DreamerZ,

How would I user the last row/last column info to adjust my print area?

Thanks,
Shawn

 

[Loomah]

Hi
As for finding the last row/column there are two FAQs on this subject (mine is faq707-2112 !!)

To use this to setup a print area you could use the following which sets the print area to those cells that thave been used. You can adapt this to use A1 as the first cell in your print area.

Sub SetDynamicPrintArea()
  Dim LastRow, LastCol, FirstRow, FirstCol

  ' Find the last real row
    LastRow = ActiveSheet.Cells.Find(What:="*", _
      SearchDirection:=xlPrevious, _
      SearchOrder:=xlByRows).Row

  ' Find the last real column
    LastCol = ActiveSheet.Cells.Find(What:="*", _
      SearchDirection:=xlPrevious, _
      SearchOrder:=xlByColumns).Column

  ' Find the first real row
    FirstRow = ActiveSheet.Cells.Find(What:="*", _
      SearchDirection:=xlNext, _
      SearchOrder:=xlByRows).Row

  ' Find the first real column
    FirstCol = ActiveSheet.Cells.Find(What:="*", _
      SearchDirection:=xlNext, _
      SearchOrder:=xlByColumns).Column
    
    ActiveSheet.PageSetup.PrintArea = Range(Cells(FirstRow, FirstCol), Cells(LastRow, LastCol)).Address
End Sub

;-)

If a man says something and there are no women there to hear him, is he still wrong?
The faqs ma'am, just the faqs. Get the best from these forums : faq222-2244

 

[shawnmandel]

Hi Loomah,

Your code makes perfect sense. However, I'm getting the following error on the following line:

LastRow = ActiveSheet.Cells.Find(What:="*", _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByRows).Row


"Error: Object variable or With block variable not set."


I must be missing something, because your code appears perfect.


Thanks,
Shawn

 

[shawnmandel]

Just to let you know that my MS Office 10.0 Object Library & MS Forms 2.0 Object Library are selected in my References.

Thanks,
Shawn

 

[DreamerZ]

Change the LastRow to:

Set LastRow = ActiveSheet.Cells.Find(What:="*", _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByRows)

and it should work. Then use the PageSetup.PrintArea as Loomah says. Except add .row and .column to the LastRow range (Cells(LastRow.row, LastCol.column)).


DreamerZ
simplesolutions@prodigy.net
[ignore]

http://www.geocities.com/simplesolutions@prodigy.net

[/ignore]

 

[Loomah]

Shawn
I'm stumped by this!
If you comment out that line does your code set LastCol, FirstRow, FirstCol etc? I'm assuming you copied out the code from the post as is?

Office10, that's XP isn't it? The search syntax didn't change did it?

Also, your last thread has me wondering. You are running this code from within XL aren't you? If not can you post back what you have and where you are running it from.

Shouldn't make a difference but you could try DIMing the variables properly, Rows as Long, Cols as Integer. What happens if the code is all placed on one line? You may have guessed I'm just guessing now as I can't think why it won't work.

I have to go now but I'll check in the morning.

;-)


If a man says something and there are no women there to hear him, is he still wrong?
The faqs ma'am, just the faqs. Get the best from these forums : faq222-2244

 

[PHV]

I guess your XL code is during OLE Automation.
If so, try this:
LastRow = yourXLapp.ActiveSheet.Cells.Find(What:="*", _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByRows).Row
and so on.

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[shawnmandel]

DreamerZ, thanks for your suggestion, however, it did not work. I'm still getting the error.

Loomah, I'm running my code from withing XL, yes. I'm using Office XP. As far as I know, the search syntax did not change (I will confirm that, though). I've copied and pasted your code directly from your post. I tried DIMing the variables with no luck.

PHV, I tried Application.ActiveSheet.Cells.Find(What:="*", _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByRows).Row
... and so on. However, no luck--still erroring out with "Object variable or With block vriable not set."

Thanks for all of your suggestions. It's highly appreciated!

Regards,
Shawn

 

[shawnmandel]

Hi,

Using your various suggestions, I modified my code to the following and now I only get the error on the last line -- the .PageSetup.PrintArea function:

Sub SetDynamicPrintArea()
Dim LastRow, LastCol, FirstRow, FirstCol

' Find the last real row
Set LastRow = ActiveSheet.Cells.Find(What:="*", _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByRows)

' Find the last real column
Set LastCol = ActiveSheet.Cells.Find(What:="*", _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByColumns)

' Find the first real row
Set FirstRow = ActiveSheet.Cells.Find(What:="*", _
SearchDirection:=xlNext, _
SearchOrder:=xlByRows)

' Find the first real column
Set FirstCol = ActiveSheet.Cells.Find(What:="*", _
SearchDirection:=xlNext, _
SearchOrder:=xlByColumns)

ActiveSheet.PageSetup.PrintArea = Range(Cells(FirstRow.Row, FirstCol.Column), Cells(LastRow.Row, LastCol.Column)).Address
End Sub


The problem is that each variable (e.g., LastRow, LastCol, FirstRow, and FirstCol) are all set to a value of Nothing. I'm not sure whether my changes even make sense.

I hope this helps.


Thanks,
Shawn

 

[Loomah]

Shawn
If the variables are being set as Nothing then it suggests you have no data in the ActiveSheet which, on reflection, might have been the problem all along.

Using the version you have above the variables start as EMPTY but after the search they are set to NOTHING which allows the code to continue. In my original version they start as EMPTY and remain that way which is why the error occurs on the search code.

Is that the case?

;-)

If a man says something and there are no women there to hear him, is he still wrong?
The faqs ma'am, just the faqs. Get the best from these forums : faq222-2244

 

[shawnmandel]

Loomah,

That is exactly the case. However, my ActiveSheet contains all kinds of data.

Thanks for your response,
Shawn

 

[shawnmandel]

Loomah,

My ActiveSheet was protected! As soon as I remove the protection, I got results for the variables. I believe that your subroutine is now functioning fine.

Thanks very much!

Shawn

 

[shawnmandel]

Loomah,

Because my ActiveSheet was protected, it's like the subroutine was seeing no data. Thus, you're correct in assuming that my ActiveSheet had "no data". It was this observation that lead me to find out that my sheet was protected.

Cheers!
Shawn