Problems copying Autofiltered rows & SpecialCells
 

Hi,

I am filtering a selection based on one column NOT being zero using
Autofilter. The rows that are not zero have to be copied.

Unfortunately, it is possible for no rows to be grater than zero, so
SpecialCells returns no rows to copy and I get an error...

Here is my code:
Selection.AutoFilter Field:=7, Criteria1:="<0.00000000"

[B3:C122,G3:G122].Select

Selection.SpecialCells(xlCellTypeVisible).Copy ' <<<< ERROR HERE

How can I do this without an error... that is, check the number of rows that
are visible in my range and if it's zero, do something else...

thanks

Philip

Problems copying Autofiltered rows & SpecialCells
 

Dim rng as Range
Selection.AutoFilter Field:=7, Criteria1:="<0.00000000"

[B3:C122,G3:G122].Select
set rng = Nothing
On Error Resume Next
set rng = Selection.SpecialCells(xlCellTypeVisible)
on Error goto 0
if not rng is nothing then
rng.copy


end if

--
Regards,
Tom Ogilvy


"Philip" wrote in message
...
Hi,

I am filtering a selection based on one column NOT being zero using
Autofilter. The rows that are not zero have to be copied.

Unfortunately, it is possible for no rows to be grater than zero, so
SpecialCells returns no rows to copy and I get an error...

Here is my code:
Selection.AutoFilter Field:=7, Criteria1:="<0.00000000"

[B3:C122,G3:G122].Select

Selection.SpecialCells(xlCellTypeVisible).Copy ' <<<< ERROR HERE

How can I do this without an error... that is, check the number of rows
that
are visible in my range and if it's zero, do something else...

thanks

Philip

Problems copying Autofiltered rows & SpecialCells
 

Looks like Tom beat me to the punch but here is my crack at it... Pretty much
the same as Tom's...

Sub test()
Dim rng As Range
Dim wks As Worksheet

Set wks = ActiveSheet

Selection.AutoFilter Field:=7, Criteria1:="<0.00000000"

On Error Resume Next
Set rng = wks.Range("B3:C122,G3:G122").SpecialCells(xlCellTy peVisible)
On Error GoTo 0

If rng Is Nothing Then
MsgBox "No Cells"
Else
rng.Copy
End If

End Sub

HTH

"Philip" wrote:

Hi,

I am filtering a selection based on one column NOT being zero using
Autofilter. The rows that are not zero have to be copied.

Unfortunately, it is possible for no rows to be grater than zero, so
SpecialCells returns no rows to copy and I get an error...

Here is my code:
Selection.AutoFilter Field:=7, Criteria1:="<0.00000000"

[B3:C122,G3:G122].Select

Selection.SpecialCells(xlCellTypeVisible).Copy ' <<<< ERROR HERE

How can I do this without an error... that is, check the number of rows that
are visible in my range and if it's zero, do something else...

thanks

Philip

Problems copying Autofiltered rows & SpecialCells
 

thanks guys that's great.

Philip

"Jim Thomlinson" wrote:

Looks like Tom beat me to the punch but here is my crack at it... Pretty much
the same as Tom's...

Sub test()
Dim rng As Range
Dim wks As Worksheet

Set wks = ActiveSheet

Selection.AutoFilter Field:=7, Criteria1:="<0.00000000"

On Error Resume Next
Set rng = wks.Range("B3:C122,G3:G122").SpecialCells(xlCellTy peVisible)
On Error GoTo 0

If rng Is Nothing Then
MsgBox "No Cells"
Else
rng.Copy
End If

End Sub

HTH

"Philip" wrote:

Hi,

I am filtering a selection based on one column NOT being zero using
Autofilter. The rows that are not zero have to be copied.

Unfortunately, it is possible for no rows to be grater than zero, so
SpecialCells returns no rows to copy and I get an error...

Here is my code:
Selection.AutoFilter Field:=7, Criteria1:="<0.00000000"

[B3:C122,G3:G122].Select

Selection.SpecialCells(xlCellTypeVisible).Copy ' <<<< ERROR HERE

How can I do this without an error... that is, check the number of rows that
are visible in my range and if it's zero, do something else...

thanks

Philip