jweese74

New Member
Joined
May 5, 2005
Messages
25
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
  5. 2011
  6. 2010
  7. 2007
Platform
  1. 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"?

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.
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
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.
 
Upvote 0
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.
 
Upvote 0
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:
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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 :)
 
Upvote 0
You are welcome.
Glad you got it all working!
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,207
Members
448,554
Latest member
Gleisner2

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
Back
Top