I am using excel 2000

I am using this code to print staff holiday forms:


Sub PrintHolidayForms()

Dim lngLoop As Long
Dim rngItems As Range

Set rngItems = Worksheets("Holiday Entitlement").Range("ConsultantName")


Worksheets("Holiday Form").Select
ActiveSheet.Unprotect
For lngLoop = 1 To rngItems.Rows.Count

Range("E3") = rngItems.Cells(lngLoop, 1)
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True

Next
ActiveSheet.Protect



End Sub


The range ConsultantName is a dynamic range and contains the staff names, starting in cell B4, the range also contains the words "Vacant" and "Proposed", how do I stop the Vacant and Proposed printing

eg in the list below I only want it to print a form for John and Fred
John
Vacant
Proposed
Fred

Thx

Paul

I solved this by copying and pasting the column of names to a blank worksheet, editing and replacing "Vacant" and "Proposed" with "", to leave blank cells and then used code to remove the blank cells from the list

Thx for looking

Try editing your loop as follows...

For lngLoop = 1 To rngItems.Rows.Count

If rngItems.Cells(lngLoop, 1) < "Vacant" _
And rngItems.Cells(lngLoop, 1) < "Proposed" Then
Range("E3") = rngItems.Cells(lngLoop, 1)
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
End If

Next

HTH

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc