Delete Rows II

[JohnUK]

Hi, I had some fantastic help from Gary’s Student some time back, whereas he
put together a piece of code that deletes rows from the bottom of the page
upwards:

Sub RowKiller()
Dim r1 As Range, r2 As Range
Dim n As Long
n = Cells(Rows.Count, "K").End(xlUp).Row
m = n + 10
Set r1 = Range("K" & n)
Set r2 = Range("K" & m & ":K" & Rows.Count)
If r1.Value <> "" Then
r2.EntireRow.Delete
End If
End Sub

Now, this worked well using Office 2003 because the amount of lines was only
65,000 whereas 2007 is in excess of 100,000 and has now slowed down the
process admittedly by only 50 seconds, but I would have expected a speed
increase not decrease.
Is there something else I can try, maybe a different type of code for 2007
use?
As usual, help greatly appreciated
John

 

[Patrick Molloy]

add

Application.ScreenUpdating = False

just after the DIM statements. It may help

 

[Joel]

Deleting one row at a time is very slow. the faster way is to put an X in an
auxilary column for rows to be deleted and then sort the X's to top of
worksheet. but in this case you are only deleting 10 rows. I think Gary got
the range backwards. try this code which changes one line. "m" is larger
than Rows.count by 10. With Grary code you have something like this

Range("K1000:K990").entirerow.delete

My change is this

Range("K990:K1000").entirerow.delete


from
Set r2 = Range("K" & m & ":K" & Rows.Count)

to
Set r2 = Range("K" & Rows.Count & ":K" & m)

 

[JohnUK]

Thanks Guys for your help.
The code I have showing was Gary's original, I had made adjustments to it
that included Application.ScreenUpdating = False.
The problem I have is that I create different workbooks with different
ranges and what Gary had done was what I wanted at the time whereas it
deleted all rows upwards apart from the last 10 rows.
Joel, looking at yours, is it possible to delete from a given row upwards.
For example, the code would look for row 4000 and delete upwards leaving the
data + the 10 rows?
John

 

[Joel]

the general formula is this

StartRow = 2
LastRow = Range("K" & Rows.Count).end(xlup).row
Rows(startRow & ":" & LastRow).Delete

you also can do this

Rows((startRow + 10) & ":" & (LastRow - 5)).Delete