Getting Runtime error 9 - Subscript out of range

Question

I am getting run time error when I run the following code.

Sub em()
    Dim strlogin As String
    Dim strnextlogin As String

    For i = 2 To Range("A1").End(xlDown).Row
        strlogin = Sheets("Untitled").cell(i, 1)
        strnextlogin = Sheets("Untitled").cell(i + 1, 1)
        If (strlogin = strnextlogin) Then
            Rows(i).Delete
            i = i - 1
        End If
    Next i
End Sub

Can you say what line gives the error, my assumption is the code cannot find sheets("Untitled") — Davesexcel, Sep 25 '16 at 11:22

score 5 · Answer 1 · edited May 23 '17 at 11:55

I see couple of problems

You are using xlDown to find the last cell. I would recommend seeing THIS link on how to find last row.
You are getting the error Runtime Error 9 Subscript out of range because Excel cannot find the sheet that you are referring to. Please ensure that the sheet exists. If you can visually see the Untitled sheet then I believe the sheet name has leading or trailing spaces.
Once you solve that issue, the next error that you will get is Runtime error 438: Object doesn't support this property or method. And that is because you are using cell instead of cells. For example .cell(i, 1) should be .Cells(i, 1) and .cell(i + 1, 1) should be .Cells(i + 1, 1)
Declare your objects else if Sheets("Untitled") is not the active sheet then Rows(i).Delete will delete from the wrong sheet ;)
Avoid deleting the rows in a loop. It will only make your code slower. See how I have used delRange in the code below.

Fix these things and you will be good to go

Note:

Option Explicit

Sub em()
    Dim delRange As Range
    Dim ws As Worksheet
    Dim i As Long, LRow As Long

    Set ws = ThisWorkbook.Sheets("Untitled")

    With ws
        LRow = .Range("A" & .Rows.Count).End(xlUp).Row

        For i = LRow To 2 Step -1 '~~> Even 2 To LRow will work :)
            If .Cells(i, 1) = .Cells(i + 1, 1) Then
                If delRange Is Nothing Then
                    Set delRange = .Rows(i)
                Else
                    Set delRange = Union(delRange, .Rows(i))
                End If
            End If
        Next i

        If Not delRange Is Nothing Then delRange.Delete
    End With
End Sub

Getting Runtime error 9 - Subscript out of range

1 Answers1