How to fix runtime error in VBA For Loop

Question

I am executing the following code but getting error - runtime error - 6 overlfow.

Seams like rowcount is getting the perfect value, i can see when i go to Debug mode. But nothing happening after that.

can some please solve this for me?

Private Sub CommandButton1_Click()
        Dim i As Integer
        Dim RowCount As Long

        RowCount = Cells(Rows.Count, "A").End(xlUp).Row

        For i = RowCount To 2 Step -1
            If Cells(i, 4) = "7" Then
                Rows(i).Delete Shift:=xlUp
            End If
        Next i
    End Sub

Where does the error occur, and how many rows are you iterating through? If over 32768, that'd be the problem. i is an integer and can't hold values higher than that. If that's it, Dim i as Long and you're good to go. — Steve Rindsberg
– Steve Rindsberg, Commented Sep 6, 2013 at 2:44

Jon Crowell · Accepted Answer · 2013-09-06 03:48:16Z

While the comment and answer suggesting your problem is a data type issue are both technically correct, there is a much better way of handling this, especially if you are looping through a range with that many rows.

Instead of looping, simply filter the entire range and delete any rows that match your criteria.

The following code autofilters Sheet1, finding any cells in column D that equal 7. It deletes those rows, and then turns off the autofilter, leaving the rows you want. Note that by using the offset method, you won't delete row 1 whether it has a 7 in it or not.

Add this code to a module, and call the module from your button-click:

Sub DeleteRowsThatMatch()
    Dim ws As Worksheet
    Dim rng As Range
    Dim lastRow As Long
    Dim match As String

    Set ws = ThisWorkbook.Sheets("Sheet1")

    lastRow = ws.Range("D" & ws.Rows.Count).End(xlUp).Row
    match = "7"

    Set rng = ws.Range("D1:D" & lastRow)

    ' filter and delete all but header row
    With rng
        .AutoFilter field:=1, Criteria1:="=" & match
        .Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
    End With

    ' turn off the filters
    ws.AutoFilterMode = False
End Sub

Gary's Student · Accepted Answer · 2013-09-06 02:46:00Z

0

First thing I would try:

Dim I as Long

answered Sep 6, 2013 at 2:46

Gary's Student

96.9k10 gold badges66 silver badges104 bronze badges

Collectives™ on Stack Overflow

How to fix runtime error in VBA For Loop

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related