How to set range to autofiltered visible cells?

[Madiya]

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]

Here's some code that I've used before:

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

 

[Madiya]

Here's some code that I've used before:

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

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

 

[Madiya]

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]

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.

 

[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).

 

[Madiya]

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).

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]

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.