Rob,

Here's my code.

Private Sub ProcessIndividuals()
Dim strPath As String
Dim PriorityExcel As Excel.Application
Dim PrioritySheet As Excel.Worksheet
Dim IndividualRange As Range
Dim IndividualCell As Range
Dim PriorityIndividualRange As Range

Dim strIndividual As String

strPath = ThisWorkbook.ActiveSheet.Range("DirectoryName") &
ThisWorkbook.ActiveSheet.Range("FileName")

Set PriorityExcel = New Excel.Application
With PriorityExcel 'open the "priority" file
.Workbooks.Open Filename:=strPath
Set myWorkbook = .ActiveWorkbook
End With

PriorityExcel.Visible = True ' turn this only only to see what is going on

' Process each name in the Individual range on the master spreadsheet
Set IndividualRange = ThisWorkbook.ActiveSheet.Range("Individuals")
For Each IndividualCell In IndividualRange
Select Case IndividualCell.Value
Case "start", "end" ' do not process the start and end keywords
Case Else
If blnDebug Then Debug.Print IndividualCell.Value ' print the value of
the cell to see what it is
strIndividual = IndividualCell.Value ' save the cell value in a
variable to make code easier to read
' process all sheets in the "slave" workbook
For Each PrioritySheet In PriorityExcel.ActiveWorkbook.Worksheets
Select Case PrioritySheet.Name
Case "Calendar", "Awaiting approval" ' do not look for individuals
on these two worksheets
Case Else
' let's go looking for the individual on the active sheet
PriorityExcel.Worksheets(PrioritySheet.Name).Activ ate
PrioritySheet.Range("B:B").AutoFilter _
field:=1, _
Criteria1:=strIndividual, _
VisibleDropDown:=False
Set PriorityIndividualRange =
PrioritySheet.UsedRange.SpecialCells(xlCellTypeVis ible)
Debug.Print PriorityIndividualRange.Columns.Count
Debug.Print PriorityIndividualRange.Rows.Count '<----- not
showing what I expect - is showing just 1
Selection.AutoFilter
If blnDebug Then Debug.Print PrioritySheet.Name
End Select
Next PrioritySheet
End Select
Next IndividualCell
'Save and close the workbook and sheet
myWorkbook.Close savechanges:=xlSaveChanges
PriorityExcel.Quit
Set PrioritySheet = Nothing
Set myWorkbook = Nothing
Set PriorityExcel = Nothing
End Sub


This line should produce a row count. What I see is a count of one. I
should see more than one and a variable amount based on the individual
selected.
Debug.Print PriorityIndividualRange.Rows.Count

Can you help?

--
Rick Allison
wrote in message
oups.com...
If I understand correctly...

- You have a sheet containing a number of rows
- When the Autofilter is applied this number is obviously reduced and
you are left with a selection particular selection of existing rows
- You want to grab this filtered selection as a range so you can work
with it?

If this is the case then I think this should work.

<after autofilter has been applied

Set MyRange = Sheet1.UsedRange.SpecialCells(xlCellTypeVisible)


I think that should work. By using the UsedRange property it will
return a range starting with the upper left most point in the
spreadsheet with data in it, right down the lower right most point with
data in it.

If you only wish to return a certain selection of columns though,
you'll need to adjust it to read:-

Sheet1.Range("B1:L" &
Sheet1.UsedRange.Rows.Count).SpecialCells(xlCellTy peVisible)

Obviously replace the "B1:L" to whatever is needed.

I hope I've got the right idea.

Rob