On Oct 5, 6:04*pm, Madiya wrote:
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

OK Dave. It was my mistake in cut paste. I got it working now.
Can you pl help me understand the diff in 2 lines below?
ActiveSheet.Range("$A$1:$AK$2230").AutoFilter Field:=18 ' it works
ActiveSheet.RNG.AutoFilter Field:=18, Criteria1:="30162431" ' it
gives error.

Thanks again.

Regards,
Madiya