Rather than deleting the rows one at a time, create a range object
that contains all the rows to be deleted, and then do the delete in
one operation. E.g.,
Dim RangeToDelete As Range
For x = LastRow To 2 Step -1
If .Cells(x,1).Value < 1 Or .Cells(x,1) 12 Then
If RangeToDelete Is Nothing Then
Set RangeToDelete = .Rows(x)
Else
Set RangeToDelete =
Application.Union(RangeToDelete,.Rows(x))
End If
End If
Next x
If RangeToDelete IsNot Nothing Then
RangeToDelete.Delete
End If
This uses only one Delete operation, which is much faster than
deleting one row at a time.
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)
On Tue, 17 Mar 2009 11:29:07 -0700, Jake
wrote:
I'm using this code to delete unwanted rows, testing column A and leaving
values between 1 and 12, in a database:
LastRow = wksSheet.Cells(Rows.Count, "A").End(xlUp).Row
For x = LastRow To 2 Step -1
' If .Cells(x, 1).Value < 1 Or .Cells(x, 1) 12 Then .Cells(x,
1).EntireRow.Delete
' Next x
it's effective, but takes a lot of time.
Is there a faster method for this?
thanks,
Jake