On Oct 5, 5:05*pm, Dave Peterson wrote:
Here's some code that I've used befo

Dim VisRng As Range 'near the top of your code
Dim myCell as range

With ActiveSheet
* With .AutoFilter.Range 'don't worry about the exact address
* * If .Columns(1).Cells.SpecialCells(xlCellTypeVisible). Count = 1 Then
* * * *'only headers are visible
* * * *Set VisRng = Nothing
* * Else
* * * 'resize to avoid the header
* * * 'and come down one row
* * * 'single column of visible cells
* * * Set VisRng = .Resize(.Rows.Count - 1, 1).Offset(1, 0) _
* * * * * * * * * * * * * *.Cells.SpecialCells(xlCellTypeVisible)
* * End If
* End With
End With

If VisRng Is Nothing Then
* * 'do nothing
Else
* * 'do what you want here
* * for each mycell in visrng.cells
* * * * msgbox mycell.address
* * next mycell
End If

On 10/05/2011 04:40, Madiya wrote:









I am apply autofilter with criteria "aaa" on column 18 which gives me
4 rows visible.
Then I am trying to assign filtered visible rows (except top header
row) to a range but getting error of object required.

I have tried different variations of below code.

Set RNG = WSSR.Range("A1").AutoFilter(Field:=18,
Criteria1:=COLSHIP.Item(1))

RNG and WSSR are already defined.
COLSHIP is collection of unique values.
Applying autofilter on one of the value in COLSHIP which is in column
18.

Pl help.

Regards,
Madiya

--
Dave Peterson

Thanks Dave.
While trying to use your code, I am gettting error
"Object variable or with block variable not set"
on line
With .AutoFilter.Range 'don't worry about the exact address

Any idea?

Thanks again.
Madiya