Conditional Printing

jweese74

New Member
Joined
May 5, 2005
Messages
24
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,418
Office Version
365
Platform
Windows
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

New Member
Joined
May 5, 2005
Messages
24
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,418
Office Version
365
Platform
Windows
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?
 
Last edited:

jweese74

New Member
Joined
May 5, 2005
Messages
24
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,418
Office Version
365
Platform
Windows
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.
Code:
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

New Member
Joined
May 5, 2005
Messages
24
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,418
Office Version
365
Platform
Windows
What is it returning for your value of r?

You can find it by inserting a Message Box in your code, i.e.
Code:
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

New Member
Joined
May 5, 2005
Messages
24
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,418
Office Version
365
Platform
Windows
You are welcome.
Glad you got it all working!
 

Forum statistics

Threads
1,081,574
Messages
5,359,704
Members
400,545
Latest member
Damntheman30

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top