| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Slow macros cont.....
Hi All
From my original example (see the bottom of the page) I have replaced Rows("600:600").Select Selection.Delete Shift:=xlUp with Rows("600:600").Delete Shift:=xlUp This has helped a considerable amount, however, it zooms through about 150 to 200 rows then slows again. Excel 2002 is a real pain at the moment. can any one advise me further Cheers Original Example Sub Delete_Row_600_551() Sheets("Data").Select If Range("G600") < Range("Q1") Then Rows("600:600").Select Selection.Delete Shift:=xlUp End If If Range("G600") Range("S1") Then Rows("600:600").Select Selection.Delete Shift:=xlUp End If If Range("G599") < Range("Q1") Then Rows("599:599").Select Selection.Delete Shift:=xlUp End If If Range("G599") Range("S1") Then Rows("599:599").Select Selection.Delete Shift:=xlUp End If End Sub |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Slow macros cont.....
Hi there,
Have you tried using this: Application.ScreenUpdating = False at the start of your macro? If you use this, YOU MUST add this to the end of the macro. Application.ScreenUpdating = True If you don't, you won't be able to use excel until you restart it. Which is very annoying! Cheers, Foss -----Original Message----- Hi All From my original example (see the bottom of the page) I have replaced Rows("600:600").Select Selection.Delete Shift:=xlUp with Rows("600:600").Delete Shift:=xlUp This has helped a considerable amount, however, it zooms through about 150 to 200 rows then slows again. Excel 2002 is a real pain at the moment. can any one advise me further Cheers Original Example Sub Delete_Row_600_551() Sheets("Data").Select If Range("G600") < Range("Q1") Then Rows("600:600").Select Selection.Delete Shift:=xlUp End If If Range("G600") Range("S1") Then Rows("600:600").Select Selection.Delete Shift:=xlUp End If If Range("G599") < Range("Q1") Then Rows("599:599").Select Selection.Delete Shift:=xlUp End If If Range("G599") Range("S1") Then Rows("599:599").Select Selection.Delete Shift:=xlUp End If End Sub . |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Slow macros cont.....
Hi
Yes I've included Screenupdate, Automatic Calculation, Page Breaks etc, its actually a bug in excel when you automate a task it slows down, however I'm looking for a work-around. cheers -----Original Message----- Hi there, Have you tried using this: Application.ScreenUpdating = False at the start of your macro? If you use this, YOU MUST add this to the end of the macro. Application.ScreenUpdating = True If you don't, you won't be able to use excel until you restart it. Which is very annoying! Cheers, Foss -----Original Message----- Hi All From my original example (see the bottom of the page) I have replaced Rows("600:600").Select Selection.Delete Shift:=xlUp with Rows("600:600").Delete Shift:=xlUp This has helped a considerable amount, however, it zooms through about 150 to 200 rows then slows again. Excel 2002 is a real pain at the moment. can any one advise me further Cheers Original Example Sub Delete_Row_600_551() Sheets("Data").Select If Range("G600") < Range("Q1") Then Rows("600:600").Select Selection.Delete Shift:=xlUp End If If Range("G600") Range("S1") Then Rows("600:600").Select Selection.Delete Shift:=xlUp End If If Range("G599") < Range("Q1") Then Rows("599:599").Select Selection.Delete Shift:=xlUp End If If Range("G599") Range("S1") Then Rows("599:599").Select Selection.Delete Shift:=xlUp End If End Sub . . |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Slow macros cont.....
|
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Slow macros cont.....
You don't have to restart.
Type Application.ScreenUpdating = True in the VBA immediate window and hit return. Foss wrote: Hi there, Have you tried using this: Application.ScreenUpdating = False at the start of your macro? If you use this, YOU MUST add this to the end of the macro. Application.ScreenUpdating = True If you don't, you won't be able to use excel until you restart it. Which is very annoying! Cheers, Foss -----Original Message----- Hi All From my original example (see the bottom of the page) I have replaced Rows("600:600").Select Selection.Delete Shift:=xlUp with Rows("600:600").Delete Shift:=xlUp This has helped a considerable amount, however, it zooms through about 150 to 200 rows then slows again. Excel 2002 is a real pain at the moment. can any one advise me further Cheers Original Example Sub Delete_Row_600_551() Sheets("Data").Select If Range("G600") < Range("Q1") Then Rows("600:600").Select Selection.Delete Shift:=xlUp End If If Range("G600") Range("S1") Then Rows("600:600").Select Selection.Delete Shift:=xlUp End If If Range("G599") < Range("Q1") Then Rows("599:599").Select Selection.Delete Shift:=xlUp End If If Range("G599") Range("S1") Then Rows("599:599").Select Selection.Delete Shift:=xlUp End If End Sub . |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Slow macros cont.....
I wish I already knew that..
Thanks very much Cheers Fos ----- JWolf wrote: ---- You don't have to restart Type Application.ScreenUpdating = Tru in the VBA immediate window and hit return Foss wrote Hi there Have you tried using this Application.ScreenUpdating = Fals at the start of your macro If you use this, YOU MUST add this to the end of the macro Application.ScreenUpdating = Tru If you don't, you won't be able to use excel until you restart it. Which is very annoying Cheers Fos -----Original Message---- Hi Al From my original example (see the bottom of the page) I have replace Rows("600:600").Selec Selection.Delete Shift:=xlU wit Rows("600:600").Delete Shift:=xlU This has helped a considerable amount, however, it zooms through about 150 to 200 rows then slows again. Excel 2002 is a real pain at the moment can any one advise me furthe Cheer Original Exampl Sub Delete_Row_600_551( Sheets("Data").Selec If Range("G600") < Range("Q1") The Rows("600:600").Selec Selection.Delete Shift:=xlU End I If Range("G600") Range("S1") The Rows("600:600").Selec Selection.Delete Shift:=xlU End I If Range("G599") < Range("Q1") The Rows("599:599").Selec Selection.Delete Shift:=xlU End I If Range("G599") Range("S1") The Rows("599:599").Selec Selection.Delete Shift:=xlU End I End Su |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Excel 2007 - Slow & can't run macros | Excel Discussion (Misc queries) | |||
| Macros Running Sometimes Slow Sometimes Fast | Excel Discussion (Misc queries) | |||
| Excel 2002 - Slow macros | Excel Programming | |||
| Excel Macros Slow in XP | Excel Programming | |||
| Macros run slow in XP | Excel Programming | |||
![Sanj[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
