Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
faster method to delete rows
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
faster method to delete rows
Hi Jake
You can filter or use union http://www.rondebruin.nl/delete.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Jake" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
faster method to delete rows
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
faster method to delete rows
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,A112)" 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 -- __________________________________ HTH Bob "Jake" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
For each cell method, is there a much faster way? | Excel Programming | |||
Which method is faster | Excel Discussion (Misc queries) | |||
Looking for faster method for Copy & Paste | Excel Programming | |||
Which method is faster? | Excel Programming | |||
Any delete rows macro faster than this one??? | Excel Programming |