Delete Row out of multi-dim array

Question

i have the following code and am stuck as of now. Instead of this line, i actually want to delete the row. How to do that?

cData(rw, 5) = "Matching DES found"

For rw = 1 To UBound(cData, 1)
            'For Each e In cRng
            For rw2 = 1 To UBound(cData, 1)
                If Left(cData(rw, 1), 4) <> "DES_" Then
                    a = cData(rw, 3)

                    If Left(cData(rw2, 1), 4) = ("DES_") And Right(cData(rw2, 1), Len(a)) = a Then
                        cData(rw, 5) = "Matching DES found"


                        'cData(rw, 1) = Empty
                        Exit For
                        'GoTo nextI
                        Exit For
                   Else
                       cData(rw, 5) = "unique"
                       'GoTo nextE
                   End If
               Else
                   'GoTo nextI
                   Exit For
               End If
'nextE:
           Next
'nextI:
       Next

If you are going to be deleting rows, you will want to step backwards in your loop or you risk unintentionally skipping rows. — braX, Feb 27 '20 at 10:45
Do you have a big range where to check that specific condition? If yes, you can use a new range, make a union of specific cells to be deleted and finally delete all rows of the range at once. Otherwise, you can proceed like braX already suggested. — FaneDuru, Feb 27 '20 at 10:47
@brax okay, so whenever the if condition gets executed, i will reset rw=rw-1. but first...how do i delete the rows? — , Feb 27 '20 at 11:27
https://stackoverflow.com/questions/7851859/delete-a-row-in-excel-vba — braX, Feb 27 '20 at 11:31
Does this answer your question? [Delete a row in Excel VBA](https://stackoverflow.com/questions/7851859/delete-a-row-in-excel-vba) — braX, Feb 27 '20 at 11:31
Is cData a range or an array? If it is a VBA array (as you wrote), there is no method for doing that directly. You have to create a new array with the desired rows. I would suggest using a `Dictionary` object to do that, adding only the rows that you want to the object, and then creating the new array from the object. You should also be able to use the `ArrayList` object as an intermediary, but I haven't worked much with that. — Ron Rosenfeld, Feb 27 '20 at 11:36
@braX not exactly, because i loop through an array and deleting an row in the sheet, will mess up my array — , Feb 27 '20 at 11:51

EvR · Accepted Answer · 2020-02-27T12:43:44.173

Here a solution with the use of a ListBox in memory: (deleting backwards)

Set ListBoxData = CreateObject("New:{8BD21D20-EC42-11CE-9E0D-00AA006002F3}") 'Listbox
ListBoxData.List = cData
For rw = ListBoxData.ListCount - 1 To 0 Step -1
            'For Each e In cRng
            For rw2 = ListBoxData.ListCount - 1 To 0 Step -1
                    If Left(ListBoxData.List(rw, 0), 4) <> "DES_" Then
                    a = ListBoxData.List(rw, 2)

                    If Left(ListBoxData.List(rw2, 0), 4) = "DES_" And Right(ListBoxData.List(rw2, 0), Len(a)) = a Then
                        ListBoxData.List(rw, 4) = "Matching DES found"
                ListBoxData.RemoveItem rw 'remove your row

                        'ListBoxData(rw, 1) = Empty
                        Exit For
                        'GoTo nextI
                        'Exit For
                   Else
                       ListBoxData.List(rw, 4) = "unique"
                       'GoTo nextE
                   End If
               Else
                   'GoTo nextI
                   Exit For
               End If
'nextE:
           Next
'nextI:
       Next
       newcData = ListBoxData.List 'cleaned Listboxdata to a new Array, but lbound = 0 so act accordingly

as Object (or when you have an Userform in your sheet you could use MSForms.ListBox) — EvR, Feb 27 '20 at 13:01
Could not set the list property. invalid property array index --> Error . at ListboxData.List = cData — , Feb 27 '20 at 13:04
You have to use use your cData as in your previous code or set your Data from the sheet directly to the ListBoxList. My answer is to replace the code you've posted in the OP — EvR, Feb 27 '20 at 13:08

Delete Row out of multi-dim array

1 Answers1