VBA Runtime Error 9 while using Array in Excel

Question

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

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

I suggest instead doing For i = lbound(myArray) to ubound(myArray), that may help. — BruceWayne
– BruceWayne, Commented Jun 30, 2016 at 20:25

YowE3K · Accepted Answer · 2016-06-30 20:37:52Z

1

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.

answered Jun 30, 2016 at 20:37

YowE3K

24k7 gold badges28 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

VBA Runtime Error 9 while using Array in Excel

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related