Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Is there a quick way ?
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 ! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Is there a quick way ?
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 ! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Is there a quick way ?
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 ! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Is there a quick way ?
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 ! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Is there a quick way ?
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 ! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Is there a quick way ?
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 ! |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Is there a quick way ?
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" wrote: 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 ! |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Is there a quick way ?
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. "Eric @ BP-EVV" wrote: 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" wrote: 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 ! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Just a quick thank | Excel Programming | |||
Quick question - quick answer about assigning shortcut keys | Excel Programming | |||
HELP QUICK I NEED TO KNOW HOW TO ... | Excel Discussion (Misc queries) | |||
NEED HELP QUICK AGAIN! | Excel Discussion (Misc queries) | |||
Quick VB Help | Excel Programming |