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

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"
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

How do you check for hidden rows?

"Jim Cone" wrote:

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"
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

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



"RW"
wrote in message
How do you check for hidden rows?



"Jim Cone" wrote:
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"
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

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(xlcelltypevisib le).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

RW wrote:

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

--

Dave Peterson

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"

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(xlcelltypevisib le).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

RW wrote:

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

--

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 wrote:

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"

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(xlcelltypevisib le).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

RW wrote:

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

--

Dave Peterson

--

Dave Peterson

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


"Dave Peterson"

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.

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" wrote in message
...
XL 97 only has the AutoFilter Method...
XL2000 adds the AutoFilter Property which returns the AutoFilter object.
--
Jim Cone


"Dave Peterson"

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.