faster method to delete rows

[Jake]

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

 

[Ron de Bruin]

Hi Jake

You can filter or use union
http://www.rondebruin.nl/delete.htm

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm

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


__________ Information from ESET Smart Security, version of virus signature database 3943 (20090317) __________

The message was checked by ESET Smart Security.

http://www.eset.com

__________ Information from ESET Smart Security, version of virus signature database 3943 (20090317) __________

The message was checked by ESET Smart Security.

http://www.eset.com

 

[Chip Pearson]

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)

 

[Bob Phillips]

Use a filter

Sub Deletedata()
Dim wksSheet As Worksheet
Dim x As Long
Dim LastRow As Long
Dim rng As Range

Set wksSheet = ActiveSheet

With wksSheet

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
.Columns(2).Insert
Set rng = Range("A1").Resize(LastRow)
rng.Offset(0, 1).Formula = "=OR(A1<1,A1>12)"
rng.Cells(1, 1).Offset(0, 1).Value = "tmp"
rng.Offset(0, 1).AutoFilter field:=1, Criteria1:=True
On Error Resume Next
Set rng = rng.Offset(1, 0).SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If Not rng Is Nothing Then

rng.EntireRow.Delete
End If
.Columns(2).Delete
End With
End Sub