Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looping thru filtered data in steps of 10
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looping thru filtered data in steps of 10
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 "gtslabs" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Return filtered values into report worksheet based on filtered valueon the data worksheet | Excel Worksheet Functions | |||
MS2007 - deleting filtered data, deletes non-visible data too | Excel Discussion (Misc queries) | |||
Filter data and fill only filtered data | Excel Discussion (Misc queries) | |||
Help to sort out filtered data from the data contained in another sheet of the same workbook | Excel Worksheet Functions | |||
steps to eM URL? | Excel Discussion (Misc queries) |