P
Pete
I have used the code below to delete 29 of every 30 rows of data in a very
large data base of plant parameters. Each row represents one second of the
day (actually 27 hours of data logging i.e. 100,000 rows of data). This code
worked well in 2003 with 65,000+ rows but not so well in 2007.
The macro "breaks" after the line "... EntireRow.Delete". I can choose to
continue, end, or debug. If I use "select" instead of delete, the marco runs
fine.
Option Explicit
Sub Delete_rows()
Dim h As Long 'used as a fixed count in j loop
Dim i As Long 'counts number of total rows in spreadsheet
Dim k As Integer 'number of rows to be deleted
Dim m As Integer 'number of rows plus one to skip over when deleting
h = 0
i = 0
k = 29
m = 0 'leaves one row undeleted when m = 0
Application.ScreenUpdating = False
For i = 3 To 100000 'Number of rows in data sheet excludes headers
h = i + k - 1
Range("A" & i & ":" & "A" & h).EntireRow.Delete
i = i + m 'Leaves m+1 rows undeleted
Next
Application.ScreenUpdating = True
MsgBox "Progam Finished - Rows have beed deleted"
End Sub
I think this may be a bug in 2007 Excel VBA. Any thoughts would be
appreciated.
Pete
large data base of plant parameters. Each row represents one second of the
day (actually 27 hours of data logging i.e. 100,000 rows of data). This code
worked well in 2003 with 65,000+ rows but not so well in 2007.
The macro "breaks" after the line "... EntireRow.Delete". I can choose to
continue, end, or debug. If I use "select" instead of delete, the marco runs
fine.
Option Explicit
Sub Delete_rows()
Dim h As Long 'used as a fixed count in j loop
Dim i As Long 'counts number of total rows in spreadsheet
Dim k As Integer 'number of rows to be deleted
Dim m As Integer 'number of rows plus one to skip over when deleting
h = 0
i = 0
k = 29
m = 0 'leaves one row undeleted when m = 0
Application.ScreenUpdating = False
For i = 3 To 100000 'Number of rows in data sheet excludes headers
h = i + k - 1
Range("A" & i & ":" & "A" & h).EntireRow.Delete
i = i + m 'Leaves m+1 rows undeleted
Next
Application.ScreenUpdating = True
MsgBox "Progam Finished - Rows have beed deleted"
End Sub
I think this may be a bug in 2007 Excel VBA. Any thoughts would be
appreciated.
Pete