Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying nothing/everything when an autofilter returns no results? XL2003
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying nothing/everything when an autofilter returns no results? XL2003
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- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copying format controls - xl2003 | Excel Discussion (Misc queries) | |||
Terminating autofilter removes commandbuttons (xl2000, not in xl2003) | Excel Programming | |||
Copying, Pasting Autofilter results to a region more specific than a specified worksheet. | Excel Programming | |||
Switch off Sort with Autofilter in XL2003 | Excel Programming | |||
Copying And Renaming Sheets XL2003 | Excel Programming |