Variable Ranges for Print Areas

[Graham]

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.

 

[Dick Kusleika]

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