Getting a mismatch error trying to extract a row from a 2D array

[RawlinsCross]

In what I thought would be a fairly straight forward operation, I'm getting stumped with an error. I have a table on a worksheet that I have registered under a NamedRange. I simply want to push each row to a Variant variable in turn. Easy right? Clearly I'm missing something.

Private Sub RemoveDuplicateWORows()

Dim lFirst As Long, lLast As Long, i As Long
Dim vTable() As Variant
Dim vRow() As Variant
Dim wSht As Worksheet
Dim rRange As Range

Set wSht = ThisWorkbook.Worksheets("Data")
Set rRange = wSht.Range("rngData")

vTable = rRange.value

'Get first and last array positions
lFirst = LBound(vTable, 1)
lLast = UBound(vTable, 1)

For i = lFirst To lLast
    vRow = WorksheetFunction.Index(vTable, i, 0)  'mismatch error here
Next i

End Sub

The array vTable is a mixture of data types, string, date, numbers.

 

[Fluff]

Must be a version thing, as the code works for me even with large strings.
I know that some functions don't like more than 255 characters, but didn't think index was one of them.

 

[Marcelo Branco]

Must be a version thing, as the code works for me even with large strings.

Yes, you're right. Didn't work in XL 2010, but worked in XL2019.

M.

 

[RawlinsCross]

After testing and retesting, and testing again - I finally got what I needed (although Rick, your function still failed under the same limitations as you hinted it might). It's been quite interesting learning this particular limitation even though it took all morning of wondering why my small test sets worked and my actual data failed.

Anyhow, thanks for all the feedback. The original range will never be more than a hundred or so rows - therefore I used a range-based approach. I step backwards as the last duplication will be the most-correct record.

Private Sub RemoveDuplicateWORows()

Dim i As Long
Dim oDict As Object
Dim sWO As String

Set mwSht = ThisWorkbook.Worksheets("Data")
Set mrRange = mwSht.Range("rngData")
Set oDict = CreateObject("Scripting.Dictionary")

For i = mrRange.Rows.Count To 1 Step -1
    sWO = CStr(mrRange.Cells(i, 1).value)
    If Not oDict.Exists(sWO) Then
        oDict.Add sWO, ""
    Else
        mrRange.Rows(i).EntireRow.Delete
    End If
Next i

End Sub