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

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

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

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

That means that you haven't applied the autofilter to the activesheet.

The code I suggested wasn't meant as a complete solution. You'll have to merge
it into your code.

On 10/05/2011 08:04, Madiya wrote:
On Oct 5, 5:05 pm, Dave 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

--
Dave Peterson

I'm guessing that Rng is a Range variable and it's been set correctly.

If that's true, then you can't use:
ActiveSheet.RNG.AutoFilter

Try
RNG.AutoFilter

Rng already knows what it is. It knows what its parent is (the activesheet or
some other worksheet).




On 10/05/2011 08:30, Madiya wrote:
On Oct 5, 6:04 pm, wrote:
On Oct 5, 5:05 pm, Dave 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

--
Dave Peterson

On Oct 6, 4:47*pm, Dave Peterson wrote:
I'm guessing that Rng is a Range variable and it's been set correctly.

If that's true, then you can't use:
ActiveSheet.RNG.AutoFilter

Try
RNG.AutoFilter

Rng already knows what it is. *It knows what its parent is (the activesheet or
some other worksheet).

On 10/05/2011 08:30, Madiya wrote:









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

--
Dave Peterson

Well Dave,
I have again tried but failed.
I have defined range as below.
Set RNG = Range(Cells(2, COSHIP), Cells(LR, COSHIP))
where each variable contains a integer value.

Next statement is as below
RNG.AutoFilter Field:=18, Criteria1:=COLSHIP.Item(1)

which gives error as
Run-time error 1004
"Autofilter method of range class failed"

This is just to understand where I am mistaking.

Regards,
Madiya

I'd replace this line:
RNG.AutoFilter Field:=18, Criteria1:=COLSHIP.Item(1)
with real values in the criteria range.

And make sure that the range has 18 columns.

You may want to verify that all your variables in the "set rng" statement are
what you expect, too.

On 10/07/2011 05:49, Madiya wrote:
On Oct 6, 4:47 pm, Dave wrote:
I'm guessing that Rng is a Range variable and it's been set correctly.

If that's true, then you can't use:
ActiveSheet.RNG.AutoFilter

Try
RNG.AutoFilter

Rng already knows what it is. It knows what its parent is (the activesheet or
some other worksheet).

On 10/05/2011 08:30, Madiya wrote:









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

--
Dave Peterson

Well Dave,
I have again tried but failed.
I have defined range as below.
Set RNG = Range(Cells(2, COSHIP), Cells(LR, COSHIP))
where each variable contains a integer value.

Next statement is as below
RNG.AutoFilter Field:=18, Criteria1:=COLSHIP.Item(1)

which gives error as
Run-time error 1004
"Autofilter method of range class failed"

This is just to understand where I am mistaking.

Regards,
Madiya

--
Dave Peterson