Run-time error '9': Subscript out of range

[Noxerus]

I am new to using Arrays. I have written a macro to remove rows with a value that is captured from a column of data on a different worksheet in the same workbook. See code below:

Sub Test()
    Dim LastRow As Long, I As Long
    Dim locRng As Range, FoundCell As Range
    Dim locCode As Variant
    Dim shSheet1 As Worksheet, shSheet2 As Worksheet
 
    Set shSheet1 = Worksheets("Resource")
    Set shSheet2 = Worksheets("Raw Data")
 
    LastRow = Cells(65536, 1).End(xlUp).Row
    Set locRng = shSheet2.Range("E2:E" & LastRow)
    locCode = shSheet1.Range("M2:M14").Value
    With shSheet2
        .Select
        With locRng
            For I = LBound(locCode) To UBound(locCode)
                Do
                    Set FoundCell = locRng.Find(What:=locCode(I), _
                                               After:=.Cells(.Cells.Count), _
                                               LookIn:=xlFormulas, _
                                               LookAt:=xlWhole, _
                                               SearchOrder:=xlByRows, _
                                               SearchDirection:=xlNext, _
                                               MatchCase:=False)
                    If FoundCell Is Nothing Then
                        Exit Do
                    Else
                        FoundCell.EntireRow.Delete
                    End If
                Loop
            Next I
        End With
    End With
 
End Sub

The locCode(I) is what is giving me the Subscript out of Range error. Any help would be greatly appreciated.

 

[jimrward]

arrays hold a number of items in a list, so you could declare an array with 3 items like so
dim myfruit(2) as string
myfruit(0) = "apple"
myfruit(1) = "orange"
myfruit(2) = "pear"

you have not declared any elements for your array and therefore cannot address it in your code with locode(I), and since it is not declared as an array it has no lbound or ubound, whereas my example lbound(myfruit) = 0, ubound(myfruit)=2

 

[Norie]

locCode will be a 2-dimensional array, the first dimension being the rows the 2nd the columns.

eg if you populated it from A1:C10 it would be 1 to 10 by 1 to 3.

In your case it'll be 1 to 13 by 1 to 1.

So instead of locCode(I) for the search term use locCode(I, 1).

 

[Ruddles]

My suggestion: change locCode = shSheet1.Range("M2:M14").Value to locCode = Application.Transpose(shSheet1.Range("M2:M14")).

You can't load an array like you were doing AFAIAA.

 

[Noxerus]

Thank you all so much for the information. Ruddles you simple fix worked perfectly. I have one last question. My range includes a blank cell because I want to have rows with blanks cells removed as well. Is there a way to represent a blank in the array?

Thanks again everyone.

 

[Norie]

I'm not 100% sure if you can use Find to locate blanks, you probably can though.

Anyway, instead of using Find you could try another approach.

What about looping (backwards) through all the rows and deleting those that have a blank in column E or match a value in the array?

The brute force method to check if the value in E matches one in the array would be to loop through the array but there are other ways to do it eg using Application.Match.