Prevent getting all rows if Autofilter criteria doesn't exist

[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

 

[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

 

[Guest]

How do you check for hidden rows?

 

[Jim Cone]

Sub IsItFiltered()
Dim lngAll As Long
Dim lngSome As Long
lngAll = ActiveSheet.UsedRange.Rows.Count
lngSome = ActiveSheet.UsedRange.Columns(1).SpecialCells(xlCellTypeVisible).Count
MsgBox "Filtered is " & (lngAll <> lngSome)
End Sub
--
Jim Cone
San Francisco, USA
http://www.officeletter.com/blink/specialsort.html



"RW" <[email protected]>
wrote in message
How do you check for hidden rows?

 

[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

 

[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

 

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

 

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

 

[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

 

[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" <peter_t@discussions>
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"

 

[Dave Peterson]

I'm just happy that some of the brain synapses are still firing. <bg>

 

[Dave Peterson]

And I meant my synapses! <vvbg>

I'm just happy that some of the brain synapses are still firing. <bg>

 

[Guest]

Thanks everyone. I'll test this tomorrow at work.

And I meant my synapses! <vvbg>

 

[Peter T]

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

Regards,
Peter T