Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
rogue rows appear in autofiltered sort | Excel Discussion (Misc queries) | |||
Copying to an autofiltered list | Excel Discussion (Misc queries) | |||
Deleting AutoFiltered Rows | Excel Programming | |||
Count Autofiltered Rows? | Excel Programming | |||
Help to hide rows, SpecialCells(xlCellTypeBlanks) doesn't work... | Excel Programming |