jweese74

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

Some videos you may like

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,882
Office Version
  1. 365
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"?
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
25
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
55,882
Office Version
  1. 365
Platform
  1. 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
25

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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,882
Office Version
  1. 365
Platform
  1. 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
25

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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,882
Office Version
  1. 365
Platform
  1. 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
25
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
55,882
Office Version
  1. 365
Platform
  1. Windows
You are welcome.
Glad you got it all working!
 

Watch MrExcel Video

Forum statistics

Threads
1,122,230
Messages
5,594,949
Members
413,953
Latest member
Arthur1471

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