Filter and Copy

Seamus2626
Seamus2626 used Ask the Experts™
on
Hi,

I have a piece of code that filters and copyies data.

The problem is when there is no daa to copy, i get the error

"Object Required"

I thought by saying
        If Not rData Is Nothing Then
        Exit Sub

It would leave the sub but it doesnt!

Can anyone advise?

Thanks
Seamus
With ActiveSheet
    .AutoFilterMode = False
    .Range("A1").AutoFilter Field:=8, Criteria1:="CA"
    With .AutoFilter.Range
        On Error Resume Next
        Set rData = .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible)
        On Error GoTo 0
        If Not rData Is Nothing Then
        Exit Sub
        Else
            rData.EntireRow.Copy
        End If
    End With

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
NorieAnalyst Assistant

Commented:
Seamus

You've got the logic the wrong way round.

It should be:


If Not rData Is Nothing Then

      rData.EntireRow.Copy
Else

      Exit sub
End If

Open in new window

Top Expert 2010
Commented:
Try this instead:


With ActiveSheet
    .AutoFilterMode = False
    .Range("A1").AutoFilter Field:=8, Criteria1:="CA"
    With .AutoFilter.Range
        On Error Resume Next
        Set rData = .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible)
        If Err <> 0 Then
            Exit Sub
        Else
            rData.EntireRow.Copy
        End If
        On Error GoTo 0
    End With

Open in new window


Author

Commented:
Thanks imnorie, but  that gave me the same result

Thank you Matthewspatrick, that worked well

Seamus

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial