Moving rows into another worksheet

[blinks]

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

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

[Siddharth Rout]

Two things...

1) Use

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"?

[blinks]

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)

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

[Siddharth Rout]

** 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

Range("A1").Select
Selection.Copy

can also be written as

Range("A1").Copy

[blinks]

Thanks so much koolsid! sheets("China") worked!

Now to apply all your various suggestions all together and see how performance has improved ...

:-)

[blinks]

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?

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

[blinks]

Sorry, forgot to mention ... Application.ScreenUpdating=False is set in the macro which calls SubExtractRecorsd

[Siddharth Rout]

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

Lets stop complaining about Microsoft and look for a solution for problems created by us...

Did you consider the option of paste special which I suggested earlier? That will speed up the code tremendously

Sidz

[blinks]

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 -

Sheets("data").Range("A1").Copy