PDA

View Full Version : Solved: best way to delete 18,500 rows?



DaveK
09-16-2008, 09:33 AM
I am coding a VBA to process a weekly report, in which I process an excel file which contains the result of a database query. My starting .xls file usually has about 50,000 rows of data, and after a sort to find all the resulting rows with certain criterea, I usually have 20,000 rows which I simply need to delete before processing the remaining rows.

ActiveSheet.Cells.Sort Key1:=Range("I2"), Order1:=xlAscending, Key2:=Range("O2"), Order2:=xlAscending, _
Header:=xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, _
DataOption2:=xlSortNormal

Set c = Range("I:I").Find(What:="SAS", LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)

c.Activate
first_row_of_SAS = c.Row 'this is the row number of the very first SAS record


There are also rows of data to be deleted below the needed rows.

What is the best way to delete all these rows?

I know I should start probably at the BOTTOM ROW and delete the rows backwards moving UP the sheet, until I hit the last row of what I need to keep.

Then, maybe re-sort again, so the unwanted rows are again at the bottom, and delete them as done before.

How is the best way to do this?

THANK YOU
Dave

Kenneth Hobs
09-16-2008, 09:58 AM
Is sorting really needed? You might try using a filter and then use a macro to delete the hidden rows. Not sure why you have to delete data though. Your query should be able to get just what you need.

DaveK
09-16-2008, 01:24 PM
Gosh... I have to work with the query that exists... as others use it too. So I can't change the query to get JUST exactly what I need for the report I am creating.....

I suppose I don't HAVE TO SORT the data... but it seemed to me that the SORT would offer a headstart of knowing what rows to delete.

This is the brute force way I coded to get it to work... but what I was asking is... if there is a more elegant (effecient) way to delete all the 20,000+ unneeded rows.



total_number_of_rows = Cells(Rows.Count, "I").End(xlUp).Row

i = 2

For k = 2 To total_number_of_rows

If Cells(i, "I") <> "SAS" Then
.Rows(i).Delete
Else
i = i + 1
End If
Next


I was thinking that after doing the sort, then I could get the first row of where the needed records are beginning... and then find the last row of the records I need, etc... and then maybe delete all the other rows somehow.


ActiveSheet.Cells.Sort Key1:=Range("I2"), Order1:=xlAscending, Key2:=Range("O2"), Order2:=xlAscending, _
Header:=xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, _
DataOption2:=xlSortNormal

Set c = Range("I:I").Find(What:="SAS", LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)


c.Activate
first_row_of_SAS = c.Row 'this is the row number of the very first SAS record



The brute force way does work... just felt the experts here would know a more effecient way to do it... since I need to delete 20,000+ rows every week in the VBA.

THANKS for any help
Dave

Kenneth Hobs
09-16-2008, 01:54 PM
Without checking this, you can do something like this. You can set screenupdating to false and manual calculation to speed the macro if needed.

Sub KeepISAS()
Dim total_number_of_rows As Long, i As Long
total_number_of_rows = Cells(Rows.Count, "I").End(xlUp).Row

For i = total_number_of_rows To 2 Step -1
If Cells(i, "I") <> "SAS" Then
Rows(i).Delete xlUp
End If
Next
End Sub

DaveK
09-16-2008, 03:00 PM
Thank you for the reply and your suggestions... the going backwards Loop seems slightly simpler, and maybe therefore a touch faster.

I thought there might have been a way to set a range of all the unwanted rows and then clear the range... but this looping through every row DOES WORK... it just crunches away for a good 60+ seconds, which I suppose is ok.

Thank you for the help!
Dave

Kenneth Hobs
09-16-2008, 05:43 PM
Did the speed tips that I recommended not help?

I submitted these speed tips to the KB. Put this in a Module. The 2nd part is how to use them.

Public glb_origCalculationMode As Integer

Sub SpeedOn(Optional StatusBarMsg As String = "Running macro...")
glb_origCalculationMode = Application.Calculation
With Application
.Calculation = xlCalculationManual
.ScreenUpdating = False
.EnableEvents = False
.DisplayAlerts = False
.Cursor = xlWait
.StatusBar = StatusBarMsg
.EnableCancelKey = xlErrorHandler
End With
End Sub

Sub SpeedOff()
With Application
.Calculation = glb_origCalculationMode
.ScreenUpdating = True
.EnableEvents = True
.DisplayAlerts = True
.CalculateBeforeSave = True
.Cursor = xlDefault
.StatusBar = False
.EnableCancelKey = xlInterrupt
End With
End Sub

Sub KeepISAS()
Dim total_number_of_rows As Long, i As Long
total_number_of_rows = Cells(Rows.Count, "I").End(xlUp).Row

SpeedOn
For i = total_number_of_rows To 2 Step -1
If Cells(i, "I") <> "SAS" Then
Rows(i).Delete xlUp
End If
Next
SpeedOff
End Sub

mdmackillop
09-17-2008, 12:32 AM
I would go with the Sort solution.
Do the Sort. Do two Finds for SAS, one with SearchDirection xlNext, the other with xlPrevious. This will give the start and end cells which you can use to delete the selected rows as in

Range(cStart, cEnd).EntireRow.Delete

Paul_Hossler
09-17-2008, 06:28 AM
My experience is that if the rows to be deleted are scattered, Excel seems to delete a block, re-pack (?), delete another block, re-pack, etc. Takes forever, even if I use a high to low row delete loop

One thing I'd suggest that that if the original order is important, or the data is not re-sortable, that the OP add a temp column with a sequence number that can be used to return the remaining rows to the original order.

Of course .Calculation = xlCalculationManual and .ScreenUpdating = False and .EnableEvents = False should be there also


Paul

DaveK
09-17-2008, 02:00 PM
I would go with the Sort solution.
Do the Sort. Do two Finds for SAS, one with SearchDirection xlNext, the other with xlPrevious. This will give the start and end cells which you can use to delete the selected rows as in

Range(cStart, cEnd).EntireRow.Delete


This is more like it! I KNEW there had to be a better way than brute force delete every row one-at-a-time. (The delete of all these rows way took maybe 1 second)!

Now I just need a bit more help... as this idea is on the right path... EXCEPT that of course this DELETES ALL THE DESIRED ROWS!

I am trying to find a way to DELETE EVERYTHING ELSE...
I have tried this, but it delete the top HEADER ROW... so this still isnt right.


ActiveSheet.Cells.Sort Key1:=Range("I2"), Order1:=xlAscending, Key2:= _
Range("O2"), Order2:=xlAscending, Header:=xlYes, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal

Set c = WS.Cells.Find(What:="SAS", LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False

Range("I2", c).Offset(-1).EntireRow.Delete


So, after the SORT is done, (which takes only 1 second), I need to delete all rows from ROW 2 (save the header row), to the ROW JUST ABOVE where c is.

We could use c.Address maybe? Then how to define 1 row above it?

I need help on the bottom remaining rows tooo...

total_number_of_rows = Cells(Rows.Count, "I").End(xlUp).Row

Set r = Cells(total_number_of_rows, "I").Address

Range(c, r).EntireRow.Delete



Something like this... but this DOES NOT WORK... but you see the idea.

Your help is veryyy much appreciated!

THANKS. We are getting close.

Dave

PS. this executes in just 1-2 seconds.. now we just need to get the ranges right so the Range(c, r).EntireRow.Delete works on what we need!

Kenneth Hobs
09-17-2008, 05:07 PM
In your test, how many rows did you sort?

You can attach a short example xls or add one to a free shared site like 4shared.com, box.net or mediafire.com. It just makes it a bit easier when we use the same information and code.

This is probably a good project to look at the most efficient means to do many row deletions. Of course the best route is to just query what you need to begin with. Another query, advanced filter or such might be worth looking at as well.

Kenneth Hobs
09-18-2008, 11:57 AM
Some of these ideas were detailed well at: http://www.rondebruin.nl/delete.htm

f2e4
09-19-2008, 02:37 AM
How about this:

The code looks through Column I and stores all the rows you are looking for then deletes them in one lump sum.

I always find this process better than a loop, but its up to you.


Dim cell As Range
Dim rng As Range
Dim firstaddress As String
With ActiveSheet
On Error Resume Next
With .Columns(9)
'This is the first cell to start searching
Set cell = .Find(What:="SAS", after:=Cells(c.row, 9), _
LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext)
On Error GoTo 0
If Not cell Is Nothing Then
firstaddress = cell.Address
Set rng = cell
Do
With cell
If .Value = "SAS" Then
Set rng = Union(cell, rng)
End If
End With
Set cell = .FindNext(cell)
Loop Until cell Is Nothing Or cell.Address = firstaddress
If Not rng Is Nothing Then rng.EntireRow.Delete
End If
End With
End With
Set cell = Nothing
Set rng = Nothing

Krishna Kumar
09-19-2008, 03:31 AM
Hi,

Dim StartSAS As Range, EndSAS As Range, r As Range
Set r = Range("i1", Range("i" & Rows.Count).End(xlUp))
With r
.Sort , Key1:=.Cells(2, 1), Order1:=xlAscending
End With
Set StartSAS = r.Find("SAS", after:=r.Cells(1, 1), lookat:=xlPart, searchdirection:=xlNext)
Set EndSAS = r.Find("SAS", after:=StartSAS, lookat:=xlPart, searchdirection:=xlPrevious)
Range(Cells(StartSAS.Row, "i"), Cells(EndSAS.Row, "i")).EntireRow.Delete