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 !

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 !

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 !

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 !

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

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 !

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

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

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 !