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

[JoHio2577]

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.

 

[kennypete]

Try this:

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.

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

 

[JoHio2577]

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]

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.

Before @Print@ button clicked:

At the breakpoint (you can see the filter applied):

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]

I am now receiving an error message stating "Ambiguous Name" and it is pointing to Sub printRows. Thoughts?

 

[DataBlake]

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]

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]

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.