CODEWITHSUNDEEP
vbaexcel
nightcrawler
nightcrawler

Reputation: 327

VBA Runtime Error 9 while using Array in Excel

Below is a picture representing efficiency versus date of a given machine. Efficiencies aren't done daily but on random dates. This efficiency is then repeated till a new efficiency is added. So in our case unique efficiencies are done on Day# 1,3,10 & 12. However you can see that efficiencies might repeat so the Day# 3 & 12 efficiency is same ..we can't use command Remove Dublicates

enter image description here

The mission is to select those Dates where efficiency was entered on Day# 1,3,10 & 12, Using VBA I was somewhat able to do this, but it throws Error 9 at the end?

Sub AutoEffRemove()
Dim rangeToUse As Range, cell1 As Range, i As Integer, RowCount As Integer, MyArray() As Variant
Set rangeToUse = Selection
    For Each cell1 In Selection
        If cell1.Value = cell1.Offset(-1, 0).Value Then
        ReDim Preserve MyArray(RowCount)
        MyArray(RowCount) = cell1.Row
        RowCount = RowCount + 1
        End If
    Next cell1
    For i = 0 To WorksheetFunction.Count(MyArray)
    Rows(MyArray(i)).EntireRow.Delete
    'MsgBox MyArray(i)
    Next i
End Sub

MsgBox & Count(MyArray) do represent that code is targeting the required rows

Upvotes: 0

Views: 95

Answers (1)

YowE3K
YowE3K

Reputation: 23974

The error is caused because your final For loop should either range from lbound(MyArray) to ubound(MyArray), or from 0 to WorksheetFunction.Count(MyArray)-1.

Also, your final loop will not be deleting the rows you are expecting to remove. Once the first row is removed (row 4 in your example), all other cells will have shifted up one row, so the next deletion (of row 6) will actually remove what was originally row 7.

So you will be better off performing your final loop from ubound(MyArray) to lbound(MyArray) step -1.

Upvotes: 1

Related Questions