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

Answer 1

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

Answer 2

First thing I would try:

Dim I as Long