Looping thru filtered data in steps of 10

[gtslabs]

I am trying to print out a form filled in with data from an autofilter
worksheet.
The form has the option to print out UP TO 10 sets of data (rows) in
predefined cells.

In some cases I only have 1 row (or less than 10) to print and in
others I can have many (10-100+).
So I am looking for some logic to fill this form as many times as
needed and print out.
I am using the debug statement for the guts of the code of what to
printing temporarly.
I have my sheet filtered and I know how many rows are available to
print.


Sub TempPrint()
Dim rng As Range

Sheets("Data").Select
Selection.AutoFilter Field:=18, Criteria1:="WC" ' Filter page
based on criteria

i = 0
Set rng = Worksheets("Data").AutoFilter.Range.Columns(1) 'Set range
to filtered
For Each Cell In rng.SpecialCells(xlVisible)
'loop thru only visible cells
i = i + 1
If i = 10 Then Debug.Print Cell.Row, Cell.Value; Cell.Offset(0, 17), i
' temporary for main printing code

'But I need to print if all rows are used and there are less than 10
'or print the first 10, then fill in the next set up to 10 and print
looping until
'all cell.row are exhausted. Would I need a STEP 10 in a for loop?

Next
End Sub

 

[Bernie Deitrick]

Below is a macro that shows how to step through in groups of ten.

HTH,
Bernie
MS Excel MVP


Sub TempPrint2()
Dim rng As Range
Dim i As Integer
Dim Cell As Range
Dim msg As String

Sheets("Data").Select
Selection.AutoFilter Field:=18, Criteria1:="WC" ' Filter page based on criteria

i = 0
Set rng = Worksheets("Data").AutoFilter.Range.Columns(1) 'Set range to filtered
For Each Cell In rng.SpecialCells(xlVisible)
'loop thru only visible cells
i = i + 1
msg = msg & Chr(10) & Cell.Row & " " & Cell.Value & " " & Cell.Offset(0, 17) & " " & i
If i = 10 Then
MsgBox msg
i = 0
msg = ""
End If

' temporary for main printing code

'But I need to print if all rows are used and there are less than 10
'or print the first 10, then fill in the next set up to 10 and print looping until
'all cell.row are exhausted. Would I need a STEP 10 in a for loop?

Next Cell
If msg <> "" Then MsgBox msg

End Sub