Prevent getting all rows if Autofilter criteria doesn't exist


G

Guest

I am using the following code to cycle through criteria. However, if the data
does not match the criteria, I get all of the rows pasted to the new sheet.
How can this be prevented?
WshName, FCriteria and FCriteria2 are arrays. This code works when the data
contains the 2 criteria.

For a = y To z
Sheets(WshName(x)).Select
Range("B1").Select
Selection.AutoFilter
Selection.AutoFilter Field:=2, Criteria1:=FCriteria(a)
Range("C1").Select
Selection.AutoFilter Field:=3, Criteria1:=FCriteria2(a)
Application.CutCopyMode = False
Range("A2:AV" & EndCell).Select
Selection.Copy
Sheets(WshName(a)).Select
Range("A2").Select
ActiveSheet.Paste
Range("A2").Select
Next a
 
Ad

Advertisements

J

Jim Cone

Check the data for hidden rows, if none then no filtering took place.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware


"RW" <[email protected]>
wrote in message
I am using the following code to cycle through criteria. However, if the data
does not match the criteria, I get all of the rows pasted to the new sheet.
How can this be prevented?
WshName, FCriteria and FCriteria2 are arrays. This code works when the data
contains the 2 criteria.

For a = y To z
Sheets(WshName(x)).Select
Range("B1").Select
Selection.AutoFilter
Selection.AutoFilter Field:=2, Criteria1:=FCriteria(a)
Range("C1").Select
Selection.AutoFilter Field:=3, Criteria1:=FCriteria2(a)
Application.CutCopyMode = False
Range("A2:AV" & EndCell).Select
Selection.Copy
Sheets(WshName(a)).Select
Range("A2").Select
ActiveSheet.Paste
Range("A2").Select
Next a
 
D

Dave Peterson

Untested, it checks to see how many cells are visible in the first column of the
autofilter range. If it's 1, then only the headers are visible (no data is
shown).

Then it comes down one row and copies the visible data (all columns in the
filtered range) to its destination.

(Watch out for typos!)

Dim RngF as range
dim RngV as range

with sheets(wshname(x))
set rngf = .autofilter.range
end with

if rngf.columns(1).cells.specialcells(xlcelltypevisible).cells.count = 1 then
'only headers are visible
else
with rngf
set rngv = .resize(.rows.count-1).offset(1,0) _
.cells.specialcells(xlcelltypevisible)
rngv.copy _
destination:=Sheets(WshName(a)).Range("A2")
end with
end with
 
J

Jim Cone

Hi Dave,
There is no AutoFilter object in XL97 and therefore no AutoFilter.Range
XL 2000 does have it. The last estimate, I remember seeing, was that about
40 % of Excel users do it with XL97. (probably somebody's guess)
I am wondering, after XL2007 is released, if maybe more people will
switch to XL97 <g>
Regards,
Jim Cone


"Dave Peterson"
<[email protected]>
wrote in message
Untested, it checks to see how many cells are visible in the first column of the
autofilter range. If it's 1, then only the headers are visible (no data is
shown).

Then it comes down one row and copies the visible data (all columns in the
filtered range) to its destination.

(Watch out for typos!)

Dim RngF as range
dim RngV as range

with sheets(wshname(x))
set rngf = .autofilter.range
end with

if rngf.columns(1).cells.specialcells(xlcelltypevisible).cells.count = 1 then
'only headers are visible
else
with rngf
set rngv = .resize(.rows.count-1).offset(1,0) _
.cells.specialcells(xlcelltypevisible)
rngv.copy _
destination:=Sheets(WshName(a)).Range("A2")
end with
end with
 
Ad

Advertisements

D

Dave Peterson

You sure a worksheet didn't have a .autofilter property in xl97? I don't have
xl97, but I could have sworn it did. (I would have bet that xl95 didn't, but
xl97 did.)

In any case, you could still use:
set rngF = .Range("_FilterDatabase")
And use the hidden name.
 
J

Jim Cone

XL 97 only has the AutoFilter Method...
XL2000 adds the AutoFilter Property which returns the AutoFilter object.
--
Jim Cone


"Dave Peterson"
<[email protected]>
wrote in message
You sure a worksheet didn't have a .autofilter property in xl97? I don't have
xl97, but I could have sworn it did. (I would have bet that xl95 didn't, but
xl97 did.)
In any case, you could still use:
set rngF = .Range("_FilterDatabase")
And use the hidden name.
 
P

Peter T

Hi Jim,

What am I missing, this works in xl97

Dim af As AutoFilter
ActiveCell.AutoFilter ' turn on
Set af = ActiveSheet.AutoFilter

Dave's code worked fine in my xl97

Regards,
Peter T
 
J

Jim Cone

Peter,
I just tried Dave's code using "AutoFilter.Range" in XL97
and it worked. I had assumed since it was not documented
that it would not work...not very smart. My apologies to Dave.
Regards,
Jim Cone


"Peter T" <[email protected]>
wrote in message
Hi Jim,
What am I missing, this works in xl97
Dim af As AutoFilter
ActiveCell.AutoFilter ' turn on
Set af = ActiveSheet.AutoFilter
Dave's code worked fine in my xl97
Regards,
Peter T


"Jim Cone"
 
Ad

Advertisements

Ad

Advertisements

P

Peter T

That's good to hear, afraid my neural network is getting a bit tattered !

Regards,
Peter T
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top