Hi
I'm using the advanced filter in place to filter a list based on
certain criteria and then copying the filter result into another
worksheet. It works OK if there is data matching the criteria but if
there is no matching data it copies the whole of the "list range".
Any help on how I can prevent this would be gratefully received.

'FILTERS THE IMPORTED DATA FOR THE REFERENCE NUMBERS AT EACH
DIVISION LEVEL
Criterion.Clear
FilterForLevels.FormulaR1C1 = "=LEN(R[6]C)=" & LenLevel_1 & ""


Sheets("Import Area").Select
Database.Select
Selection.AdvancedFilter Action:=xlFilterInPlace,
CriteriaRange:=Range("A3:B4"), Unique:=False

Sheets("Level_1").Select
Range("a4:e6000").Clear

Sheets("Import Area").Select
RefNumbers.Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Level_1").Select
Range("a4").Select
ActiveSheet.Paste

You can count the visible rows before and after the filter...
ActiveSheet.Cells.SpecialCells(xlCellTypeVisible). Rows.Count
--
Jim Cone
Portland, Oregon USA
http://www.contextures.com/excel-sort-addin.html




"Snuffwinkler"
wrote in message
...
Hi
I'm using the advanced filter in place to filter a list based on
certain criteria and then copying the filter result into another
worksheet. It works OK if there is data matching the criteria but if
there is no matching data it copies the whole of the "list range".
Any help on how I can prevent this would be gratefully received.

'FILTERS THE IMPORTED DATA FOR THE REFERENCE NUMBERS AT EACH
DIVISION LEVEL
Criterion.Clear
FilterForLevels.FormulaR1C1 = "=LEN(R[6]C)=" & LenLevel_1 & ""


Sheets("Import Area").Select
Database.Select
Selection.AdvancedFilter Action:=xlFilterInPlace,
CriteriaRange:=Range("A3:B4"), Unique:=False

Sheets("Level_1").Select
Range("a4:e6000").Clear

Sheets("Import Area").Select
RefNumbers.Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Level_1").Select
Range("a4").Select
ActiveSheet.Paste

I think that this will give you the number of rows visible in the first area --
not the entire range (well, if there are visible cells).



On 07/23/2010 08:17, Jim Cone wrote:
You can count the visible rows before and after the filter...
ActiveSheet.Cells.SpecialCells(xlCellTypeVisible). Rows.Count

--
Dave Peterson

You could look at the number of cells visible in the first column of the
filtered range.

dim VisRows as long
with database 'your range variable???
visrows = .columns(1).cells.specialcells(xlcelltypevisible). cells.count
end with

And since your filtered range (database) includes the headers:

if visrows = 1 then
'only header
else
'do the real work
end if



On 07/23/2010 06:30, Snuffwinkler wrote:
Hi
I'm using the advanced filter in place to filter a list based on
certain criteria and then copying the filter result into another
worksheet. It works OK if there is data matching the criteria but if
there is no matching data it copies the whole of the "list range".
Any help on how I can prevent this would be gratefully received.

'FILTERS THE IMPORTED DATA FOR THE REFERENCE NUMBERS AT EACH
DIVISION LEVEL
Criterion.Clear
FilterForLevels.FormulaR1C1 = "=LEN(R[6]C)="& LenLevel_1& ""


Sheets("Import Area").Select
Database.Select
Selection.AdvancedFilter Action:=xlFilterInPlace,
CriteriaRange:=Range("A3:B4"), Unique:=False

Sheets("Level_1").Select
Range("a4:e6000").Clear

Sheets("Import Area").Select
RefNumbers.Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Level_1").Select
Range("a4").Select
ActiveSheet.Paste

--
Dave Peterson