Moving rows into another worksheet
I've been using the following code pretty successfully in pre-2007 versions of Excel. But in 2007, it takes an hour to run, I think because Excel 2007 supports 1million rows, rather than 64K! I can change the "columns("I:I").Select to
Range("i1").Select
Range(Selection, Selection.End(xlDown)).Select
which speeds things up a bit, but it is still very slow. Any recommendations for changing the code to run faster?
TIA
Code:
Sub ExtractRecords()
'
' Copies all records containing "chinatest" in column I to Data worksheet and pastes into China worksheet. Returns to Data worksheet and
' removes copied titles
'
'
'Selects Data worksheet
Sheets("Data").Select
' Filters by URL containing "chinatest"
Columns("I:I").Select
Selection.AutoFilter
Selection.AutoFilter field:=1, Criteria1:="=*chinatest*", Operator:= _
xlAnd
'Copies all visible cells and pastes into China worksheet
Cells.Select
Selection.Copy
Sheets("china").Select
Range("A1").Select
ActiveSheet.Paste
'Returns to Data worksheet and deletes visible titles
Sheets("Data").Select
Range("A2").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Application.CutCopyMode = False
Selection.EntireRow.Delete
Selection.AutoFilter
End Sub
Re: Moving rows into another worksheet
Two things...
1) Use
vb Code:
Application.Screenupdating = False
at the beginning of the code and then set it back to 'True' at the end of the code...
2) Do you need to do a 'Paste'? This takes longer than "Paste Special Values"?
Re: Moving rows into another worksheet
Thanks koolsid. The screenUpdating did speed things up a bit, but not enough, so am trying to find more efficiencies. Testing a bit of code at a time, and have struck a hiccup, with error message "runtime error 424 object required" occuring for the below bit of code (with the asterisks)
Code:
Sub ExtractRecords()
'
' Copies all records containing "chinatest" in column I to Data worksheet and pastes into China worksheet. Returns to Data worksheet and
' removes copied titles
'
'
'Selects Data worksheet
Sheets("Data").Select
' Filters by URL containing "chinatest"
Columns("I:I").Select
Selection.AutoFilter
Selection.AutoFilter field:=1, Criteria1:="=*chinatest*", Operator:= _
xlAnd
'Copies all visible cells and pastes into China worksheet
** ActiveSheet.AutoFilter.Range.Copy Destination:=china.Range("A1")
'Returns to Data worksheet and deletes visible titles
Sheets("Data").Select
Range("A2").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Application.CutCopyMode = False
Selection.EntireRow.Delete
Selection.AutoFilter
End Sub
Re: Moving rows into another worksheet
Quote:
** ActiveSheet.AutoFilter.Range.Copy Destination:=china.Range("A1")
1) What is china in the above piece of code? If it is a variable then have you declared it properly and if it is the name of the sheet then you need to put that as sheets("China")
2) One more tip (Sample code below). This will also speed up things as you don't have to select objects every time to work with them. You can directly interact with them..
This
vb Code:
Range("A1").Select
Selection.Copy
can also be written as
Re: Moving rows into another worksheet
Thanks so much koolsid! sheets("China") worked!
Now to apply all your various suggestions all together and see how performance has improved ...
:-)
Re: Moving rows into another worksheet
Below is the final version of the code. This did speed things up, although I ended up having to revert back to compatibility mode as other parts of the code are still taking up too many resources. Still working on those bits, but in the meantime, I must say that I'm seriously disappointed in Excel 2007. I know part of the problem is the fact that my original code is not as efficient as it could be, but surely Microsoft could have built in some things to prevent these 64K-->1024K spreadsheet problems?
HTML Code:
Sub ExtractRecords()
'Filters Data worksheet by URL containing "chinatest"
Sheets("Data").Columns("I:I").AutoFilter Field:=1, Criteria1:="=*china*", Operator:= xlAnd
Cells.Select
'Copies all visible cells into China worksheet
ActiveSheet.Cells.Copy Destination:=Sheets("China").Range("A1")
'Returns to Data worksheet and deletes visible titles
'Sheets("Data").Range("A2").Select
'Range(Selection, ActiveCell.SpecialCells(xlLastCell)).EntireRow.Delete
Sheets("Data").Range("A2", Range("A2").End(xlDown)).EntireRow.Delete
'Turn off autofilter
ActiveSheet.ShowAllData
End Sub
Re: Moving rows into another worksheet
Sorry, forgot to mention ... Application.ScreenUpdating=False is set in the macro which calls SubExtractRecorsd
Re: Moving rows into another worksheet
Quote:
but surely Microsoft could have built in some things to prevent these 64K-->1024K spreadsheet problems?
One thing I could never understand, when people wanted more rows in excel, Microsoft gave it and now when people have it, they cannot stop complaining :lol:
Lets stop complaining about Microsoft and look for a solution for problems created by us... :wave:
Did you consider the option of paste special which I suggested earlier? That will speed up the code tremendously ;)
Sidz
Re: Moving rows into another worksheet
You are right, of course, about Microsoft, Sidz :)
Re the PasteSpecial, I thought I'd avoided that issue by the following line -I
ActiveSheet.Cells.Copy Destination:=Sheets("China").Range("A1")
Is that not so?
I've also found that the same macro won't always work on a different computer, or on the same computer under different circumstances (e.g. if I step thru, rather than running the whole thing). Particular culprits here seem to be lines which I've compressed to remove "select" statement such as -
Code:
Sheets("data").Range("A1").Copy