CODEWITHSUNDEEP
excelvba
prabhu ram
prabhu ram

Reputation: 21

Getting Runtime error 9 - Subscript out of range

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

Upvotes: 2

Views: 405

Answers (1)

Siddharth Rout
Siddharth Rout

Reputation: 149295

I see couple of problems

  1. You are using xlDown to find the last cell. I would recommend seeing THIS link on how to find last row.

  2. 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.

  3. 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)

  4. Declare your objects else if Sheets("Untitled") is not the active sheet then Rows(i).Delete will delete from the wrong sheet ;)

  5. 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

Upvotes: 5

Related Questions