I have a large dataset on "Worksheet A". I use VBA to apply to autofilter
criteria to the worksheet, copy the (visible) results, and paste them on to
"Worksheet B".

The problem I'm running into is that it appears that when the autofilter
criteria result in no visible rows, the entire (hidden) data set is copied
from "Worksheet A" using the code below.

Is there a good way to determine if there are usable rows once the
autofilter is applied, but before the copy/paste operation? I'd like to opt
out and not copy anything if there are no visible records.

Thank you,
Keith


Private Sub CommandButton1_Click()

'Collect sort information
SortDept = Sheet12.Range("B2").Value
SortDate = Sheet12.Range("E2").Value

'clear previous data "sheet B" before going to get new data
Sheet12.Activate
Sheet12.Rows("28:5000").Select
Selection.Delete Shift:=xlUp
Sheet12.Range("A1").Activate

'get the data from "Sheet A"
Sheet16.Activate
Sheet16.Select
Sheet16.Cells.Select
Sheet16.Range("A1").Activate
Selection.AutoFilter
Selection.AutoFilter Field:=10, Criteria1:=SortDept
Selection.AutoFilter Field:=20, Criteria1:=SortDate

'This is where I'd like to know if there are any results returned from
the autofilter

'This selects just the unhidden rows, excluding the header row
Sheet16.Range("A2:X50001").Select

Selection.Copy
Sheet12.Select
Sheet12.Range("A28").Select
ActiveSheet.Paste
Sheet16.Activate
Application.CutCopyMode = False
Selection.AutoFilter
Sheet16.Range("A1").Select
Sheet12.Activate

End Sub

SpecialCells(xlCellTypeVisible) will throw an error if no cells are visible.
--
Jim Cone
Portland, Oregon USA


"ker_01"
wrote in message
I have a large dataset on "Worksheet A". I use VBA to apply to autofilter
criteria to the worksheet, copy the (visible) results, and paste them on to
"Worksheet B".
The problem I'm running into is that it appears that when the autofilter
criteria result in no visible rows, the entire (hidden) data set is copied
from "Worksheet A" using the code below.
Is there a good way to determine if there are usable rows once the
autofilter is applied, but before the copy/paste operation? I'd like to opt
out and not copy anything if there are no visible records.
Thank you,
Keith
-snip-