获取运行时错误9 - 下标超出范围

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

1 个解决方案

#1

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.

   您正在使用xlDown查找最后一个单元格。我建议看看如何找到最后一行的这个链接。

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.

   您收到错误运行时错误9下标超出范围,因为Excel找不到您要引用的工作表。请确保表格存在。如果您可以直观地看到无标题表单,那么我认为表单名称具有前导或尾随空格。

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)

   解决该问题后,您将获得的下一个错误是运行时错误438:对象不支持此属性或方法。那是因为你使用细胞而不是细胞。例如.cell(i,1)应该是.Cells(i,1)和.cell(i + 1,1)应该是.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 ;)

   如果Sheets(“Untitled”)不是活动工作表,则声明您的对象,然后Rows(i).Delete将从错误的工作表中删除;)

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.

   避免删除循环中的行。它只会让你的代码变慢。在下面的代码中查看我如何使用delRange。

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