Problems copy data when using filter in place

[Snuffwinkler]

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

 

[Jim Cone]

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" <[email protected]>
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

 

[Dave Peterson]

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).

 

[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

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