Hi Bernie,

With this line of code:
Set FilteredClients = AllClients.SpecialCells(xlCellTypeVisible)

How do I change it so I only return the visible items in column 8?

Thanks.
Simon

"Bernie Deitrick" wrote:

If you filter out the first 100 rows of your data, and your filtered list has 10 values, then you
are trying to set FilteredClient to the visible cells of the first ten rows, all of which are
hidden.

Working with a filtered list, you should set a range to the full range (offset by 1, decreased by 1)
and then use the visible cells on that

Sub PopulateValidationLists2()
Range("ClientCells").Select
Dim theClientCode As String 'Not used
Dim FilteredClients As Range
Dim AllClients As Range

Set AllClients =
Range("CustomerSolutions").Offset(1,0).Resize(Rang e("CustomerSolutions").Rows.Count -1)

For Each Client In Range("ClientCells").Cells
Range("CustomerSolutions").AutoFilter Field:=9, Criteria1:=Client.Value
Set FilteredClients =- AllClients.SpecialCells(xlCellTypeVisible)
'Do something here with FilteredClients
Next
End Sub

HTH,
Bernie
MS Excel MVP


"Simon" wrote in message
...
Hi,

I'm having trouble assigning an AutoFiltered Range of Data to a Range
Object. Here is my Code:

Sub PopulateValidationLists()
Range("ClientCells").Select
Dim theClientCode As String
Dim FilteredClients As Range
For Each Client In Range("ClientCells").Cells
Range("CustomerSolutions").AutoFilter Field:=9, Criteria1:="" &
Client.Value & ""
Set FilteredClients = Selection.AutoFilter.Range.Offset(1,
0).Resize(Selection.AutoFilter.Range.Rows.Count - 1)
Selection.AutoFilter.SpecialCells (xlCellTypeVisible)

Next
End Sub

The problem lies with this line of code:
Set FilteredClients = Selection.AutoFilter.Range.Offset(1,
0).Resize(Selection.AutoFilter.Range.Rows.Count - 1)
Selection.AutoFilter.SpecialCells (xlCellTypeVisible)

The error I receive is:
Object Required

Any ideas on how I can get around this?

Thanks.
Simon