Yet another question... setting a dynamic print range

AndrewRossington

Board Regular
Joined
Oct 27, 2005
Messages
114
Don't be mislead... I don't even know if such a thing exists.

I'd like it to exist.

Basically what I have is a pre-formatted sheet (sheet 1) that allows for data entry at certain points on the sheet, only if corresponding cells on another sheet (sheet 2) meet a certain criteria.

I'd like to be able to set it so that:

If the first 5 cells on sheet 2 meet the criteria, then the first 5 entries on sheet 1 will be printed when print is clicked. If only the first cell on sheet 2 meets the criteria, then the first entry of sheet 1 will print, but no more.

Additionally..... if the 3rd cell on sheet 2 meets the criteria, but cells 1, 2, 4 and 5 do not, then the 3rd entry point on sheet 1 will print but not the 1st, 2nd, 4th or 5th.
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

Joe Was

MrExcel MVP
Joined
Feb 19, 2002
Messages
7,539
Show your code!


This code will automatically set the print range to the active sheet area!


Sub mySetPrntRng()
'Standard module code, like: Module1
'Select entire page upto the last column/Row that has data!

Dim LstRow&, LstCol&, myActiveRng As Range

LstRow = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row
LstCol = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Column

Set myActiveRng = ActiveSheet.Range(Cells(1, 1), Cells(LstRow, LstCol))

ActiveSheet.PageSetup.PrintArea = myActiveRng.Address
End Sub
 

AndrewRossington

Board Regular
Joined
Oct 27, 2005
Messages
114
Thanks for this.

I tried, however it selects the entire sheet even when there's no data entered.

Any suggestions?
 

AndrewRossington

Board Regular
Joined
Oct 27, 2005
Messages
114
I know what causes it...

It's goes down to the last row I formatted. For information, this is about 15 lines below the last row of text.

You know when you scroll down to the bottom of the page... when the scroll bar starts to shorten (effectively widening the default view).... how do I set it back to default?
 

Joe Was

MrExcel MVP
Joined
Feb 19, 2002
Messages
7,539

ADVERTISEMENT

Select the Row Numbers with the mouse that are below your active sheet information and Delete the Rows, if you have not customized your toolbar to include these tools: Excel ToolBar: Edit - Delete; Entire Row, OK

This will shorten the visable active sheet, back to normal!
 

AndrewRossington

Board Regular
Joined
Oct 27, 2005
Messages
114
Mate, that's brilliant.

You'll be proud of me, I went into Visual Basic and I actually combined the print command with your code.... now it sets the print area and prints it, all with the press of a button. Champion!

Anyway, thank you very much for the help with this.
 

AndrewRossington

Board Regular
Joined
Oct 27, 2005
Messages
114
Wait, I have another question... (surprise surprise!)

With regards to this code:

Sub mySetPrntRng()
'Standard module code, like: Module1
'Select entire page upto the last column/Row that has data!
Dim LstRow&, LstCol&, myActiveRng As Range

LstRow = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row
LstCol = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Column

Set myActiveRng = ActiveSheet.Range(Cells(1, 1), Cells(LstRow, LstCol))

ActiveSheet.PageSetup.PrintArea = myActiveRng.Address

ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
End Sub


Is there a way of adding to this so that the worksheet prints off in black and white? (i've tried changing the printer's default settings on my own, which doesn't work. But also, I'd like anyone else who prints it to print off in only black and white.)
 

Joe Was

MrExcel MVP
Joined
Feb 19, 2002
Messages
7,539
I think B/W is a print driver issue, it is not part of Excel VBA. You will need to do it through the print driver.

But with some printers I think you can just add:


ActiveSheet.PageSetup.BlackAndWhite = True

This does not work with all print drivers though!
 

Forum statistics

Threads
1,141,587
Messages
5,707,252
Members
421,498
Latest member
matinebi

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top