stop VBA code if condition not found

[FROGGER24]

Looking in column C I am using autofilter to find duplicates then print out sheet, will need to continue printing until condition not found

<ActiveSheet.Range("$B$13:B" & Cells(Rows.Count, 3).End(xlUp).Row).AutoFilter Field:=2, Criteria1:="A1"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
IgnorePrintAreas:=False
ActiveSheet.Range("$B$13:B" & Cells(Rows.Count, 3).End(xlUp).Row).AutoFilter Field:=2, Criteria1:="A2"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
IgnorePrintAreas:=False
ActiveSheet.Range("$B$13:B" & Cells(Rows.Count, 3).End(xlUp).Row).AutoFilter Field:=2, Criteria1:="A3"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
IgnorePrintAreas:=False
ActiveSheet.Range("$B$13:B" & Cells(Rows.Count, 3).End(xlUp).Row).AutoFilter Field:=2, Criteria1:="A4"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
IgnorePrintAreas:=False
ActiveSheet.Range("$B$13:B" & Cells(Rows.Count, 3).End(xlUp).Row).AutoFilter Field:=2, Criteria1:="A5"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
IgnorePrintAreas:=False
ActiveSheet.Range("$B$13:B" & Cells(Rows.Count, 3).End(xlUp).Row).AutoFilter Field:=2, Criteria1:="A6"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
IgnorePrintAreas:=False
ActiveSheet.Range("$B$13:B" & Cells(Rows.Count, 3).End(xlUp).Row).AutoFilter Field:=2, Criteria1:="A7"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
IgnorePrintAreas:=False
ActiveSheet.Range("$B$13:B" & Cells(Rows.Count, 3).End(xlUp).Row).AutoFilter Field:=2, Criteria1:="A8"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
IgnorePrintAreas:=False
ActiveSheet.Range("$B$13:B" & Cells(Rows.Count, 3).End(xlUp).Row).AutoFilter Field:=2, Criteria1:="A9"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
IgnorePrintAreas:=False
ActiveSheet.Range("$B$13:B" & Cells(Rows.Count, 3).End(xlUp).Row).AutoFilter Field:=2, Criteria1:="A10"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
IgnorePrintAreas:=False>

thanks for your time.

 

[DanteAmor]

I'm confused.
Your range is "$B$13:B", but for the last row with data you use column C: Cells(Rows.Count, 3). And again for the filter you use B: Field:=2.

However, try the following:

I use column B to be consistent in the data

Sub Test()
    Dim n As Long, u As Long
    n = 2
    Do While True
        ActiveSheet.Range("$[COLOR=#0000ff]B[/COLOR]$13:[COLOR=#0000ff]B[/COLOR]" & Cells(Rows.Count, [COLOR=#0000ff]2[/COLOR]).End(xlUp).Row).AutoFilter Field:=[COLOR=#0000ff]2[/COLOR], Criteria1:="A" & [COLOR=#0000ff]n[/COLOR]
        u = Range([COLOR=#0000ff]"B"[/COLOR] & Rows.Count).End(xlUp).Row
        If u = 13 Then
            MsgBox "There are no more records"
            Exit Do
        End If
        ActiveSheet.PrintOut Copies:=1, Collate:=True, IgnorePrintAreas:=False
        n = n + 1
    Loop
End Sub

 

[FROGGER24]

thanks for response. Sorry bout the confusion, I made a typo when I converted to variable length rows. My data set is in columns B-E.

I need to change these two lines of code, due to formulas being in rows 11 - 40. I need to find last used row (excluding formulas).

u = Range("B" & Rows.Count).End(xlUp).Row
If u = 13 Then (' this will need to be last used row excluding formulas)

My spreadsheet is a data dump from our lab so each bottle could have 1 test or it could have 15 test that need to be ran.

Thanks for your help!

 

[DanteAmor]

Did you try the macro?

 

[FROGGER24]

Yes, it prints out numerous blank pages. There are instances where I think it is finding the cells with formulas and printing that out as well as the cells with text. My formulas stop in row 40

 

[DanteAmor]

You could put examples of what you have on your sheet and what you expect of result or You could upload a copy of your file (with examples explained) to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.

 

[FROGGER24]

link is attached
https://www.dropbox.com/s/z3fdi3zecmmh7so/Docks Lab BCP.xlsm?dl=0

apply auto filter to column C and column E (filter for True).
in column C filter for each sample container (A1,A2,A3,A5,A6), filter/print all A1s, then filter/print all A2s, until there are no more A*s unprinted. Column C is not in order as some test have been deleted over the years. Do not want to print blank pages.

Hope this helps.

 

[FROGGER24]

trying this again:
https://www.dropbox.com/s/hgk68dgevhoa5wi/Docks Lab BCP-2.xlsm?dl=0

worksheet lab data

A10 has drop down for grade number
apply auto filter to column C and column E (filter for True).
in column C filter for each sample container (A1,A2,A3,A5,A6), filter/print all A1s, then filter/print all A2s, until there are no more A*s unprinted. Column C is not in order as some test have been deleted over the years. Do not want to print blank pages.

Hope this helps.

 

[DanteAmor]

With the following macro we ensure to print only the existing Aliquot.

Sub Test()
    Dim ws As Worksheet
    Set ws = Sheets("Lab Test")
    With CreateObject("scripting.dictionary")
        For Each Cl In ws.Range("C2", ws.Range("C" & Rows.Count).End(xlUp))
            .Item(Cl.Value) = Empty
        Next Cl
        For Each Ky In .Keys
            ws.Range("A1").AutoFilter 3, Ky
            ws.PrintOut Copies:=1, Collate:=True, IgnorePrintAreas:=False
        Next Ky
    End With
    ws.ShowAllData
End Sub

 

[FROGGER24]

Dante, thank you for your help so far. when I copied it into my workbook, I printed out 40 pages of lab test criteria. I will need the code to work on the worksheet called Lab Data