A couple of items

1) This statment should have a worksheet specified

CriteriaRange:=Range("K1:K2")

2) You can get the last used row of a column without using a worksheet
function

LastRow = Worksheets("working data").Range("A" & rows.count).end(xlup).row

rows.count is a constant = 65536 for excel 2003. It is larger for excel
2007. This is useful if code wil be run on more than one version of excel.
The line goes to the last row of the worksheet and moves up until a non-blank
cell is found.

"Eric @ BP-EVV" wrote:

I will have to give that a try...I had thought that the advanced filter
statement I wrote was working properly, but now when I execute the code it is
copying ALL data from the "working data" sheet to the "Items not in F4106"
sheet...not just those that match the criteria of K1:K2 (which by the way is
a Yes / No...I want to copy (technically move) the records with the "No"
value to the "Items not in F4106" sheet.

"Joel" wrote:

Use special cell method.

ActiveSheet.Cells.SpecialCells(xlCellTypeVisible). Copy

and

ActiveSheet.Cells.SpecialCells(xlCellTypeVisible). entirerow.delete



"Eric @ BP-EVV" wrote:

I have the following statements in a VBA procedu

rcount9 = Application.WorksheetFunction.CountA(Worksheets("w orking
data").Range("A1:A65536"))
Worksheets("working data").Range("A1:K" & rcount9).AdvancedFilter _
Action:=xlFilterCopy, _
CriteriaRange:=Range("K1:K2"), _
copytorange:=Worksheets("Items not in F4106").Range("A1:K" & rcount9)

Is there an easy way to then take the data that this advanced filter just
copied to the "Items not in F4106" workbook and delete them from their
original location in the "working data" workbook ? Both workbooks are in the
same Excel spreadsheet file.

Thanks !