Yet another question... setting a dynamic print range

[AndrewRossington]

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.

 

[Joe Was]

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]

Thanks for this.

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

Any suggestions?

 

[AndrewRossington]

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]

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]

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]

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]

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!