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.

 

[RoryA]

Change:

Dim vRow() As Variant

to:

Dim vRow As Variant

 

[RawlinsCross]

Hi Rory - yeah, same issue.

 

[RawlinsCross]

So just for a test, I replaced my entire table with some other data. In this case with the formula ="Address(Row(A1), Column(A1)) and applied that over the entire table and it worked. So what about the original data might be giving me problems?

EDIT: A went through each column in turn replacing the data with the formula up above (which is really only a random function I choose, nothing special). Column A changed, fail.... Column B changed, fail..... Column C changed, fail........ finally got to column "N" and the Index function worked. So what's in column N? I have a compound string in the form of "Text1; Text2; Number; Number | Text 1; Text 2; Number; Number |"

John

 

[RoryA]

Using vRow() should not work at all. Index returns a Variant (which may or may not contain an array) not an array of type Variant. I cannot imagine why/how it would fail with a type mismatch given that a Variant can contain anything.

 

[Marcelo Branco]

See if this does what you need

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)
    
    'ReDim vRow to store rows
    ReDim vRow(1 To lLast)
    
    'Loop
    For i = lFirst To lLast
        vRow(i) = WorksheetFunction.Index(vTable, i, 0)
    Next i
    
    'Check
    For i = lFirst To lLast
        Debug.Print Join(vRow(i), ",")
    Next i
End Sub

M.

 

[RawlinsCross]

Marcelo, thanks for your reply. That doesn't work but this is an awfully strange phenomenon that I haven't come across. I column N is for a compound string that I store in each row (30 rows). The length of each string is between 47 to 1069 characters.

When I delete the column N data (or even a portion) the Index works - I can restore the data and the code fails again. There appears to be a connection and (perhaps) it's due to the length of the strings but I don't understand why this would make an Index function fail.

 

[Rick Rothstein]

I don't know if this function will work with the size data you have, so you will have to try it out to see. It is a function that accepts a variant 2D array as its first argument and the row number in that array to remove and returns a variant 2D array with that row removed...

Function DeleteArrayRow(Arr As Variant, RowToDelete As Long) As Variant
  Dim Rws As Long, Cols As String
  Rws = UBound(Arr) - LBound(Arr)
  Cols = "A:" & Split(Columns(UBound(Arr, 2) - LBound(Arr, 2) + 1).Address(, 0), ":")(0)
  DeleteArrayRow = Application.Index(Arr, Application.Transpose(Split(Join(Application.Transpose(Evaluate("Row(1:" & (RowToDelete - 1) & ")"))) & " " & Join(Application.Transpose(Evaluate("Row(" & (RowToDelete + 1) & ":" & UBound(Arr) & ")"))))), Evaluate("COLUMN(" & Cols & ")"))
End Function

 

[RawlinsCross]

Something potentially on Index limitations that I may be seeing here?

WorksheetFunction.Index Limitations – Daily Dose of Excel

 

[Marcelo Branco]

I don't understand why this would make an Index function fail.

Yes, it failed for me too when i used a string with more than 255 characters on a cell.
Don't know why either

M.