Variable Ranges for Print Areas
 

I have a table made up of several columns and a variable number of rows
dependent on the entries required in different situations.When the table has
been completed there will be several entries in column A for example which
will be duplicated, although the values in the rows will be different. ie
Column A indentifies the field being recorded. The table is sorted by Column
A, by code, so that these entries are contiguous. ie there could be 7 rows
for Field 1, 12 rows for Field 2, etc. There could be up tp 100 Field 1
Field 2 etc. What I want to do is indentify all the entries for each of
these different row entries and set the relevent rows and columns as a print
area which I would print along with some header rows. Thus I would print a
page for the entries relating to Field 1, another for field 2 etc. I had set
up print areas using dynamic named ranges but I do not know how to indentify
and show where a row has changed from one value to another. I hope this is
understandable and if so I would value any guidance.

Kind Regards,
Graham Haughs
Turriff
Scotland.

Variable Ranges for Print Areas
 

Graham

Try this for starters

Sub PrintByField()

Dim cell As Range
Dim lCount As Long
Dim rCol As Range

'Get the last cell in column A
With Sheet1
Set rCol = .Range("a2", .Range("A" & .Rows.Count).End(xlUp))
End With

'Loop through column A
For Each cell In rCol.Cells
'If a new value
If cell.Value < cell.Offset(-1, 0).Value Then
'Count the number of similar values in col A
lCount = Application.CountIf(rCol, cell.Value)
'Resize a range and print it out
cell.Resize(lCount,
cell.Parent.UsedRange.Columns.Count).PrintOut
End If
Next cell

End Sub

--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com

"Graham" wrote in message
...
I have a table made up of several columns and a variable number of rows
dependent on the entries required in different situations.When the table
has
been completed there will be several entries in column A for example which
will be duplicated, although the values in the rows will be different. ie
Column A indentifies the field being recorded. The table is sorted by
Column
A, by code, so that these entries are contiguous. ie there could be 7 rows
for Field 1, 12 rows for Field 2, etc. There could be up tp 100 Field 1
Field 2 etc. What I want to do is indentify all the entries for each of
these different row entries and set the relevent rows and columns as a
print
area which I would print along with some header rows. Thus I would print a
page for the entries relating to Field 1, another for field 2 etc. I had
set
up print areas using dynamic named ranges but I do not know how to
indentify
and show where a row has changed from one value to another. I hope this is
understandable and if so I would value any guidance.

Kind Regards,
Graham Haughs
Turriff
Scotland.