Conditional Printing

[jweese74]

Lets say, for example I have a spreadsheet with data in column B-K, and the potential for data in row 1-100. Is there a way, using some sort of flag to tell Excel only to print rows that contain said flag in Column "A"?

Alternatively, I know a complete spreadsheet is 10 pages long, if data is only contained on the first 2 pages - is there a way to pass this information off to printing to only print 2 pages that contain data and not the entire spreadsheet?

Thanks for all the help.

 

[Joe4]

Lets say, for example I have a spreadsheet with data in column B-K, and the potential for data in row 1-100. Is there a way, using some sort of flag to tell Excel only to print rows that contain said flag in Column "A"?

What exactly does this look like?
Are all the values that need to be printed "in a row" with no gaps, and then at some point it stops and nothing below it should be printed?
Or are some of the rows that you don't want printed mixed in with the rows you do want hidden? If that is the case, you should use Filters to hide the rows you do not want hidden.

Ideally, you would have all the rows you want printed at the top, and all the rows you don't want hidden at the bottom. Then it is just a matter of determining where that "swtich" happens, and sent the Print Area to only print to that point. We can help you that, if you provide the exact criteria for determing where that switch happens.

 

[jweese74]

What exactly does this look like?
Are all the values that need to be printed "in a row" with no gaps, and then at some point it stops and nothing below it should be printed?
Or are some of the rows that you don't want printed mixed in with the rows you do want hidden? If that is the case, you should use Filters to hide the rows you do not want hidden.

Ideally, you would have all the rows you want printed at the top, and all the rows you don't want hidden at the bottom. Then it is just a matter of determining where that "swtich" happens, and sent the Print Area to only print to that point. We can help you that, if you provide the exact criteria for determing where that switch happens.

My apologies, I wasn't too clear (I am battling a terrible head cold at the moment) - you hit the nail on the head (I think). All the rows that should be printed will be at the top of the spreadsheet with the rows that may not necessarily be printed on the bottom, controlled by a switch I'll build into a cell determining whether or not that row should be printed.

Printed and non-printed rows will never be intermixed, printed rows will always appear at the top with non-printed rows at the bottom.

 

[Joe4]

OK, if you can let me know exactly what this "switch" will look like, I can probably come up with the code you need.
Also, which columns do you want to print?

 

[jweese74]

ADVERTISEMENT

OK, if you can let me know exactly what this "switch" will look like, I can probably come up with the code you need.

It can be anything honestly, I haven't put one in yet; Something in column A that checks the rest of the columns in that Row.

=if(sumif(B1:D1,">0",B1:D1)=0,"No Print","Print")

Really, as long as it can check a True/False condition it will work.

 

[Joe4]

Here you go. This is looking for the first instance of "No Print", but you can change the switch value to whatever you want.
If it cannot find it at all, it will let you know.

Sub MySetPrintAreaMacro()

    Dim switch As String
    Dim r As Long
    
'   Find first value that indicates the switch
    switch = "No Print"
    
'   Find first instance
    On Error GoTo err_chk
    r = Range("A:A").Find(What:=switch, After:=Range("A1"), LookIn:=xlFormulas, _
        LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Row
    On Error GoTo 0
        
'   Set print range for columns A-K
    ActiveSheet.PageSetup.PrintArea = "$A$1:$K$" & r - 1
    MsgBox "Print area set"
    
    Exit Sub
    
'   Error handling if it cannot find switch value
err_chk:
    If Err.Number = 91 Then
        MsgBox "Cannot find switch value of " & switch & " in column A"
    Else
        MsgBox Err.Number & ": " & Err.Description
    End If
    
End Sub

 

[jweese74]

ADVERTISEMENT

Thanks, I gave that a try and ended with "Run-time error '1004'"

Debug and it stopped on this line...
ActiveSheet.PageSetup.PrintArea = "$A$1:$L$" & r - 1

 

[Joe4]

What is it returning for your value of r?

You can find it by inserting a Message Box in your code, i.e.

Sub MySetPrintAreaMacro()

    Dim switch As String
    Dim r As Long
    
'   Find first value that indicates the switch
    switch = "No Print"
    
'   Find first instance
    On Error GoTo err_chk
    r = Range("A:A").Find(What:=switch, After:=Range("A1"), LookIn:=xlFormulas, _
        LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Row
    On Error GoTo 0
        
'   Set print range for columns A-L
[COLOR=#ff0000]    MsgBox "The value of r is: " & r[/COLOR]
    ActiveSheet.PageSetup.PrintArea = "$A$1:$L$" & r - 1
    MsgBox "Print area set"
    
    Exit Sub
    
'   Error handling if it cannot find switch value
err_chk:
    If Err.Number = 91 Then
        MsgBox "Cannot find switch value of " & switch & " in column A"
    Else
        MsgBox Err.Number & ": " & Err.Description
    End If
    
End Sub

 

[jweese74]

Thanks for the response and the help Joe4 - I figured out the issue, it was this little piece here...

LookIn:=xlFormulas

Changed it to: LookIn:=xlValues ... and all seems to work fine.

Thanks again - the Mr.Excel forums haven't failed me in 13 years now

 

[Joe4]

You are welcome.
Glad you got it all working!