Is there an easy way to delete rows in Excel?

04-29-2002, 02:28 PM

jcarlson

Guest

Posts: n/a

Is there an easy way to delete rows in Excel?

Here is the problem.

Every once in a while, I will have over 150 rows to delete. I have tried looping through all 150 rows, and deleting each one seperately, but the delete seems to slow down after a while.

For example

For i = 1 to 150
rows(i).delete
Next i

I have also tried to select the rows, and delete the selection, but I can't get my array to join correctly, I get an invalid range error. For example

Dim arrDelete() as string

For i = 1 to 150
Redim Preserve arrDelete(i)
arrDelete(i) = i & ":" & i
Next

ActiveSheet.Range(join(arrDelete, ",")).Delete

Does anyone have a better idea, or know why the delete of a single row would slow down?

Thanks
-Jeff

04-29-2002, 05:20 PM

Timbo

Green-Eyed

Super Moderator
* Guru *

Join Date: May 2001

Location: Bangkok, Thailand

Posts: 10,261

Here's something I stick into most of my projects - should be in a class really

Code:

Sub UsedRange_DeleteRows(strSheet$, intRow1%)
Dim intRow2%

On Error Resume Next

    With Sheets(strSheet$)
        intRow2% = .Cells.Find("*", .Range("A1"), , , , xlPrevious).Row
        
        If intRow2% >= intRow1% Then _
            .Rows(intRow1% & ":" & intRow2%).EntireRow.Delete
            
    End With

End Sub

__________________
"He's not the Messiah. He's a very naughty boy!" - Brian's mum

Can't find the answer? >> Try something new!
Become a Professional

04-29-2002, 05:21 PM

jcarlson

Guest

Posts: n/a

Thanks Timbo. I'll give it a try.

Have you experienced any similar problems with performance when deleting rows?

-Jeff

04-29-2002, 05:41 PM

Timbo

Green-Eyed

Super Moderator
* Guru *

Join Date: May 2001

Location: Bangkok, Thailand

Posts: 10,261

not really, especially when 'ScreenUpdating' is set to False before the action...
As you already suspected, deleting the entire range rather than 1 row at a time is way quicker!

__________________
"He's not the Messiah. He's a very naughty boy!" - Brian's mum

Can't find the answer? >> Try something new!
Become a Professional

05-01-2002, 05:55 PM

Brahmin

Guest

Posts: n/a

The following works. The example isn't the most brilliant but it certainly didn't take long to complete.

Sub x()
Delrows 2, 149
End Sub

Private Sub Delrows(i As Integer, j As Integer)
ActiveSheet.Range("A" & Trim(i) & ":A" & Trim )).EntireRow.Delete
End Sub