VBA Code to print rows containing a specific value in Column A

JoHio2577

New Member
Joined
Jan 17, 2020
Messages
8
Office Version
365
Platform
Windows
I have read through several posts and cannot find a code that will work correctly. I have a spreadsheet that has 700+ rows and 100+ columns. Column A contains a value of Y or N. I need a code , using a Toggle Button, that will print ONLY the rows that have a Y in column A. I know this can be done by using the filter sort, but I want to do this with a Toggle Button for people in my company that are not as proficient in Excel so all they need to do is hit the button and everything will work correctly.
 

Some videos you may like

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

kennypete

Board Regular
Joined
Apr 19, 2008
Messages
227
Office Version
365, 2019
Platform
Windows
Try this:

VBA Code:
Sub printRows()
    With Cells(1, 1).CurrentRegion
        .AutoFilter Field:=1, Criteria1:="Y"
    End With
    ActiveSheet.PageSetup.PrintArea = "$A$1:$H$700"
    Application.PrintCommunication = True
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, IgnorePrintAreas:=False
    Selection.AutoFilter
End Sub
I tested this with the following (the Print button assigned to the printRows macro) and it does exactly what you ask for bar not using 100 columns.

1579311996104.png


PDF output (noting I made most of the A column Ns:
1579312186016.png
 

JoHio2577

New Member
Joined
Jan 17, 2020
Messages
8
Office Version
365
Platform
Windows
Thank you. This doesn't appear to be working for me. My spreadsheet size is columns A:KA and rows 1 through 724. With page formatting, I have 276 pages if I was to print the full spreadsheet. I used the code and it is trying to print all 276 pages instead of a limited selection. My "Y and "N" values in Column A are determined through formulas. Would this have any impact on the VBA code you provided?
 

kennypete

Board Regular
Joined
Apr 19, 2008
Messages
227
Office Version
365, 2019
Platform
Windows
No, that should make no difference. I have retried it (with similar dimensions to what you have and the Y or N created by a formula - shown below) and definitely only the filtered print area is printed. Have you stepped through the macro with a breakpoint to validate that only the filtered region is what will be printed after the filter criterion is specified?

E.g.
1579542277380.png


Before @Print@ button clicked:
1579542323011.png


At the breakpoint (you can see the filter applied):
1579542355229.png


In my example file I get 462pp if non-filtered (i.e. just printing the Sheet) yet only 21pp when the macro is run.
 

JoHio2577

New Member
Joined
Jan 17, 2020
Messages
8
Office Version
365
Platform
Windows
I am now receiving an error message stating "Ambiguous Name" and it is pointing to Sub printRows. Thoughts?
 

DataBlake

Well-known Member
Joined
Jan 26, 2015
Messages
779
Office Version
2016
Platform
Windows
I am now receiving an error message stating "Ambiguous Name" and it is pointing to Sub printRows. Thoughts?
this means you have two macros with the same name. change the name a little
 

JoHio2577

New Member
Joined
Jan 17, 2020
Messages
8
Office Version
365
Platform
Windows
I believe I determined what my issue is. This worksheet prints a modified part sheet for each of my customers. I have rows and columns that I have identified as those that should appear on every page. I believe this is causing all 276 pages to print when I run the script. I took that feature off and everything is working correctly. Can the code be modified to do the same thing for Columns? If I was to have "Y" or "N" in row 1, could the code look for "Y" and only print those columns?
 

kennypete

Board Regular
Joined
Apr 19, 2008
Messages
227
Office Version
365, 2019
Platform
Windows
When you say "the same thing for columns" it's not clear to me what you mean. Is there Y and N appearing in each column too? If so, it will be possible by hiding the N columns before printing, but I think you may need to provide a screenshot of what it looks like before and what you want to see in the printed output.
 

Watch MrExcel Video

Forum statistics

Threads
1,099,030
Messages
5,466,121
Members
406,468
Latest member
Toto Li

This Week's Hot Topics

Top