Excel freezes when running code

[madpup]

I have written code for a macro, the first part of which looks at the whole worksheet (which has over 60 000 entries), prompts the user to enter a specific country name and then copies all the entries for that country in question into a separate worksheet. Here is the relevant part of that code:

Country = InputBox("Enter a country name")
NoClients = Application.WorksheetFunction.CountIf(Range("A:A"), "<>") - 1

For i = NoClients To 0 Step -1
If Cells(i + RowOffset, ColumnOffset) = Country Then
Rows(i + RowOffset).EntireRow.Copy

Worksheets("Country").Activate
Excel.Range("A2").Insert Shift:=xlDown
Worksheets("Sheet1").Activate

End If
Next i

Now the code works fine, except for when I enter Japan or USA, Excel freezes. Both have the highest number of entries in my spreadsheet. China has about 4,500 it works fine, USA has 6,500 and japan even more. I am turning screen updating off and formula calculation to manual. I really am not sure why this is occurring, am I trying to push my PC beyond its capabilities, or is there something wrong with the code?

Many thanks to anyone who can offer any advice.

[Static]

does it ever come back??
or when it freezes.. is it "done"

[westconn1]

if you address each sheet, instead of activating them when you copy, it will probably cut down the time

Country = InputBox("Enter a country name")
NoClients = Application.WorksheetFunction.CountIf(Range("A:A"), "<>") - 1
 
For i = NoClients To 0 Step -1
If Cells(i + RowOffset, ColumnOffset) = Country Then
Rows(i + RowOffset).EntireRow.Copy
 
'Worksheets("Country").Activate
sheets("Country").Range("A2").Insert Shift:=xlDown
'Worksheets("Sheet1").Activate
 
End If
Next i

don't know that will help with your problem though

pete

[zaza]

...and put a DoEvents in your big loop.

[RobDog888]

Also do a ScreenUpdating to help free cpu cycles.

'Turn updating of screen off
Application.ScreenUpdating = False
'Do your loop
'Finish loop
'Turn updating back on
Application.ScreenUpdating = True