maybe...

dim RngToCopy as range
with worksheets("somesheetnamehere")
.autofiltermode = false 'turn off any existing autofilter
.range("a1").currentregion.autofilter Field:=19, Criteria1:="<"
with .autofilter.range
if .columns(1).cells.specialcells(xlcelltypevisible). cells.count = 1 then
'only headers are visible, so skip it
else
'"remove" a row and come down 1 row
set rngtocopy = .resize(.rows.count-1,.columns.count) _
.offset(1,0)
rngtocopy.copy _
destination:=Worksheets("E-Mails").Range("A2")
end with
End with

(Untested, uncompiled. Watch for typos!)

RBLampert wrote:

WOO-HOO! 99% success, Dave! Your code works as I need it to for some of the
sorts I'm doing and I've been able to tweak it to mostly work the way I need
it to in others.

The one remaining glitch is this: there are a lot of cases in which I'll
make several (independent) sorts--or better, filters--of data from one
spreadsheet and copy the results to another, stacking each new set of
filtered data below the one(s) pasted onto the spreadsheet before. With the
code below, the first row, containing the column headers (as you correctly
assumed it would), gets copied every time. I need help to figure out some
way to NOT copy the header row every time. I've tried deleting different
pieces of the code below but either get an error message or the row still
gets copied.

I can kludge together some code to delete the excess header row copies but
I'd rather have a clean piece of code I can use repeatedly to get the right
result the first time.

Thanks for your help so far. It's been great.

"Dave Peterson" wrote:

I'd use:

with worksheets("somesheetnamehere")
.autofiltermode = false 'turn off any existing autofilter
.range("a1").currentregion.autofilter Field:=19, Criteria1:="<"
with .autofilter.range
.cells.specialcells(xlcelltypevisible).entirerow.c opy _
destination:=Worksheets("E-Mails").Range("A2")
end with
End with

I'm assuming that the range to be filtered is contiguous and starts in A1
(headers in row 1). (That's what .range("A1").currentregion does.)

I think it's a problem with the selection. If just one cell is selected, then
..specialcells will refer to the whole worksheet. If you have multiple cells
selected, then only that original selection will be looked at.

It's not unlike the way Edit|Replace works.

If you select multiple cells first, then the Replace (or Find) will only look at
that selection.

If you have only a single cell selected, then edit|replace or edit|Find will
look at all the cells on the sheet.

By using .autofilter.range, you don't have to worry about what was selected.




--

Dave Peterson