Print range for unknown number of single variable

[Deliverable7]

I found this code which works very well for my use.

As my filtered list data set includes an unknown number of "Y" i need to include a variable to relplace the fixed +5 in the code below.

Any assistance appreciated.

Sub SetPrint()
    Set rowNo = Range("A:A").Find("Y", LookIn:=xlValues, lookat:=xlWhole)
    If Not rowNo Is Nothing Then
        ActiveSheet.PageSetup.PrintArea = Range("A" & rowNo.Row & ":H" & (rowNo.Row + 5)).Address
    End If
End Sub

 

[Trebor76]

Hi there,

Here's a different way (using a filter) of doing the task:

Option Explicit
Sub Macro1()

    Dim rngFiltered As Range
    Dim lngLastRow As Long
    
    ActiveSheet.AutoFilterMode = False 'Remove all filters
    
    'Ensure there's at least one row flagged with a 'Y'
    If Evaluate("COUNTIF('" & ActiveSheet.Name & "'!A:A,""Y"")") = 0 Then
        MsgBox "There are no rows in column A flagged with a Y.", vbExclamation
        Exit Sub
    End If
    
    Application.ScreenUpdating = False
    
    lngLastRow = Cells(Rows.Count, "A").End(xlUp).Row
    
    Range("$A$1:$H$" & lngLastRow).AutoFilter Field:=1, Criteria1:="Y", Operator:=xlFilterValues
    Set rngFiltered = Range("$A$1:$H$" & lngLastRow).Offset(1, 0).SpecialCells(xlCellTypeVisible)
    ActiveSheet.PageSetup.PrintArea = rngFiltered.Address
    
    Application.ScreenUpdating = True

End Sub

The code assumes Row 1 contains headings and the data starts from Row 2.

Regards,

Robert

 

[Deliverable7]

Thanks Robert, works perfectly

 

[Trebor76]

Glad to hear it. Thanks