Excel03 - empty cells and SpecialCells ( xlVisible )

Hi,

I have a sheet of data that has the Autofilter applied and I want to get a
column of filtered data and transfer it to another sheet. The code below
works most of the time but when I try to grab a range of data and the
filtered sheet does not show any data (because I have filtered using a
criteria that does not match any data in the column under consideration - I
can't change this unfortunately) I get an Error 1004 "Cells were not found"
and the routine crashes out.

> ' Code applies autofiltering based on user entered criteria before this
> section
>
> ' Copy Task Numbers
> Set rng1 = Worksheets("FEPivot").Range("K6:K500")
> Set rng1 = rng1.SpecialCells(xlVisible)
> rng1.Copy Destination:=Worksheets("StatusSummary").Range("A29")
>
> ' Code continues...

What I want to do is if the range selected does not contain any cells with
data in just have the code step over these lines onto the next set of
scripts. Is the an approach that will allow me to do this?

Regards

Chris

Dim rng1 as Range, rng2 as Range
' Copy Task Numbers
Set rng1 = Worksheets("FEPivot").Range("K6:K500")
Set rng2 = nothing
On Error Resume Next
Set rng2 = rng1.SpecialCells(xlVisible)
On Error goto 0
if not rng2 is nothing then
rng2.Copy Destination:=Worksheets("StatusSummary").Range("A29")
end if
--
Regards,
Tom Ogilvy


"Chris Paterson" <[email protected]> wrote in message
news:[email protected]...
> Hi,
>
> I have a sheet of data that has the Autofilter applied and I want to get a
> column of filtered data and transfer it to another sheet. The code below
> works most of the time but when I try to grab a range of data and the
> filtered sheet does not show any data (because I have filtered using a
> criteria that does not match any data in the column under consideration -
I
> can't change this unfortunately) I get an Error 1004 "Cells were not
found"
> and the routine crashes out.
>
> > ' Code applies autofiltering based on user entered criteria before
this
> > section
> >
> > ' Copy Task Numbers
> > Set rng1 = Worksheets("FEPivot").Range("K6:K500")
> > Set rng1 = rng1.SpecialCells(xlVisible)
> > rng1.Copy Destination:=Worksheets("StatusSummary").Range("A29")
> >
> > ' Code continues...
>
> What I want to do is if the range selected does not contain any cells with
> data in just have the code step over these lines onto the next set of
> scripts. Is the an approach that will allow me to do this?
>
> Regards
>
> Chris
>
>

Many thanks Tom, crisis over...

"Tom Ogilvy" <[email protected]> wrote in message
news:uIrY0Y%[email protected]...
> Dim rng1 as Range, rng2 as Range
> ' Copy Task Numbers
> Set rng1 = Worksheets("FEPivot").Range("K6:K500")
> Set rng2 = nothing
> On Error Resume Next
> Set rng2 = rng1.SpecialCells(xlVisible)
> On Error goto 0
> if not rng2 is nothing then
> rng2.Copy Destination:=Worksheets("StatusSummary").Range("A29")
> end if
> --
> Regards,
> Tom Ogilvy
>
>
> "Chris Paterson" <[email protected]> wrote in message
> news:[email protected]...
>> Hi,
>>
>> I have a sheet of data that has the Autofilter applied and I want to get
>> a
>> column of filtered data and transfer it to another sheet. The code below
>> works most of the time but when I try to grab a range of data and the
>> filtered sheet does not show any data (because I have filtered using a
>> criteria that does not match any data in the column under consideration -
> I
>> can't change this unfortunately) I get an Error 1004 "Cells were not
> found"
>> and the routine crashes out.
>>
>> > ' Code applies autofiltering based on user entered criteria before
> this
>> > section
>> >
>> > ' Copy Task Numbers
>> > Set rng1 = Worksheets("FEPivot").Range("K6:K500")
>> > Set rng1 = rng1.SpecialCells(xlVisible)
>> > rng1.Copy Destination:=Worksheets("StatusSummary").Range("A29")
>> >
>> > ' Code continues...
>>
>> What I want to do is if the range selected does not contain any cells
>> with
>> data in just have the code step over these lines onto the next set of
>> scripts. Is the an approach that will allow me to do this?
>>
>> Regards
>>
>> Chris
>>
>>
>
>