Delete rows in a large table

Question

I have a large table from which I want to delete entire rows corresponding to specific value of column A. I use for loop but I am looking for a more efficient way.

Sub deleteRow()
    For i = 1 To 900000
        If Cells(i, 1) > 7 Then
            Rows(i).Select
            Selection.Delete
        End If
    Next i
End Sub

You can apply a filter and then using Range.SpecialCells(xlCellTypeVisible) you can delete all at once. That will be much faster and efficient. — Mikku
– Mikku, Commented Jun 24, 2019 at 8:18

Mikku · Accepted Answer · 2019-06-24 14:09:26Z

2

This simple Macro will work for you:

No Need for Loop

For a Table use this:

Sub deleteRow()

    With ActiveSheet.ListObjects("Table1") ' Change table name

        .Range.AutoFilter Field:=1, Criteria1:=">7"
        .DataBodyRange.Delete

    End With

End Sub

It will also show a warning, select Yes when it appears

For Simple Range Object Use this:

Sub deleteRow()

    With ActiveSheet.UsedRange

        .AutoFilter Field:=1, Criteria1:=">7"
        .Offset(1, 0).SpecialCells(xlCellTypeVisible).Delete xlShiftUp

    End With

End Sub

Useful Link with Various of Table Operation Commands. Here

edited Jun 24, 2019 at 14:09

answered Jun 24, 2019 at 8:27

Mikku

6,7043 gold badges19 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

pgSystemTester Over a year ago

What's up @mikku! Let's race! I think you'll win... but I'll set it up!

Mikku Over a year ago

Hahaha @PGCodeRider .... I am not riding Codes like you do Man! :) Enjoy the ride

pgSystemTester Over a year ago

Oh you crushed me 12 seconds vs 0 seconds... although mine didn't filter the whole sheet. Here's the racing code: pastebin.com/e8kRVXx5 I surrender! Have a good night1

Hamtash Over a year ago

@Mikku Thanks. My data is already in an excel table. how can I modify this code to use for such data? and then clear the filter to show the remaining rows?

Mikku Over a year ago

@Hamtash .. Added answer for a Table. :)

pgSystemTester · Accepted Answer · 2019-06-24 08:26:50Z

0

You should delete them all at once at the end rather than on each instance.

Try this.

Sub deleteRow()
Dim killRNG As Range
Set killRNG = Cells(Rows.Count, 1).EntireRow

For i = 1 To 900000


If Cells(i, 1) > 7 Then

Set killRNG = Union(Cells(i, 1).EntireRow, killRNG)

End If
Next i

killRNG.Delete

End Sub

answered Jun 24, 2019 at 8:26

pgSystemTester

10.1k2 gold badges28 silver badges59 bronze badges

Comments

Error 1004 · Accepted Answer · 2019-06-24 10:31:18Z

0

Some Tips:

Option Explicit

Sub deleteRow()

    Dim i As Long, LastRow As Long

    'Create a with statement refer to the sheet where data are store
    With ThisWorkbook.Worksheets("Sheet1")

        'Find LastRow of column A. There is no need to loop up to 900000
        LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

        'When you loop aiming to delete you loop from bottom to top
        For i = LastRow To 1 Step -1

            'In both ".Cells(i, 1) > 7" & ".Rows(i).EntireRow.Delete" we use "." before
            If .Cells(i, 1).Value > 7 Then
                .Rows(i).EntireRow.Delete
            End If

        Next i

    End With

End Sub

answered Jun 24, 2019 at 10:31

Error 1004

8,2883 gold badges36 silver badges58 bronze badges

Collectives™ on Stack Overflow

Delete rows in a large table

3 Answers 3

No Need for Loop

5 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

No Need for Loop

5 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related