Assigning AutoFiltered Range to Range Object

[Simon]

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

 

[Roger Govier]

Hi Simon

I hit a similar problem some long while back.
I'm not sure this is the efficient way to do it, but it got me around the
problem and i have persisted with it since.
With wss having been set as the relevant sheet, and rng1 and rng2 dimmed as
ranges

Set rng1 = wss.AutoFilter.Range.Columns(1).Cells
Set rng1 = rng1.Offset(1, 0).Resize(rng1.Rows.Count - 1, 1)
Set rng2 = rng1.SpecialCells(xlVisible)

 

[Bernie Deitrick]

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(Range("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

 

[Dave Peterson]

I'd use something like:

Option Explicit
Sub testme()
Dim wks As Worksheet
Dim VisRng As Range
Dim Client As Range
Dim ClientList As Range

Set wks = ActiveSheet

With wks
'remove any existing filter
.AutoFilterMode = False

Set ClientList = .Range("ClientCells")

For Each Client In ClientList.Cells
.Range("CustomerSolutions").AutoFilter Field:=9, _
Criteria1:="" & Client.Value & ""
With .AutoFilter.Range
If .Columns(1).Cells.SpecialCells(xlCellTypeVisible) _
.Count = 1 Then
MsgBox "only the headers are visible for client: " _
& Client.Value
Else
'resize to avoid the header
'and come down one row
Set VisRng = .Resize(.Rows.Count - 1, 1).Offset(1, 0) _
.Cells.SpecialCells(xlCellTypeVisible)
MsgBox Client.Value & vbLf & VisRng.Address(0, 0)
End If
End With
Next Client
End With
End Sub

 

[Simon]

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]

Simon,

Try

Set FilteredClients = Intersect(Range("H:H"),
AllClients).SpecialCells(xlCellTypeVisible)

OR

Set FilteredClients = AllClients.Columns(8).SpecialCells(xlCellTypeVisible)

Depends, of course, on what you mean by column 8... ;-)

HTH,
Bernie
MS Excel MVP