Is there a quick way ?

[Eric @ BP-EVV]

I have the following statements in a VBA procedure:

rcount9 = Application.WorksheetFunction.CountA(Worksheets("working
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 !

 

[Joel]

Use special cell method.

ActiveSheet.Cells.SpecialCells(xlCellTypeVisible).Copy

and

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

 

[Eric @ BP-EVV]

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]

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]

Thanks Joel ! Item #1 below solved that issue. Item # 2 below...nice to
know that, will have to use it in the future.

I'm a little concerned about the use of

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

since when I look at the worksheet where my filter is being applied, ALL
cells are visible, even the ones with the "Yes" where I have the advanced
filter code copying the "No" data. I haven't tried it, but I'm afraid it
will delete ALL rows, not just the "No" rows.

 

[Joel]

Look at the row numbers on the left side of the worksheet and you'll see that
the filter row number are missing.

 

[Eric @ BP-EVV]

In this case, the row numbers are still there....rather curious, I suppose.
There may be another way to skin this cat since the data in this "working
data" sheet is being sorted at an earlier step in the code....sorted by the
column that contains the "Yes" or "No" I am using for this step that does not
seem to be cooperating. I could simply use a countif to determine the number
of "No" rows and then use that value in conjunction with a delete row command
of some sort....does that make sense ?

 

[Joel]

What I usually do in your situation is to Record a Macro while I manually do
the advance filter using the Data - Filters - advantfilter menu item. then
copy the code into a macro and make sure it still works. Then I modifiy the
SELECTION method on the recorded macro to a variable Range method. If you
can't get the advance filter to work manually you'll never get it to work
with a macro.