Delete Row from Array

Question

I am trying to go through an array to find duplicate entries in a single column of that array and delete the entire row.

I am getting figuring out rangeStart, rangeEnd, and lastrow above this and that part is working fine.

data = Range(rangeStart, rangeEnd)

For i = lastrow - 1 To 2 Step -1
    If data(i - 1, x) = data(i, x) Then
        'Delete data(i)
    End If
Next

Any help with this would be awesome!

Easier to create a second empty array of the same size and copy only unique "rows" over. — Tim Williams
– Tim Williams, Commented Jan 31, 2014 at 16:14

Tim Williams · Accepted Answer · 2014-01-31 19:18:45Z

3

Sub RemoveDups()
Const COMPARE_COL as Long = 1
Dim a, aNew(), nr As Long, nc As Long
Dim r As Long, c As Long, rNew As Long
Dim v As String, tmp

    a = Selection.Value
    nr = UBound(a, 1)
    nc = UBound(a, 2)

    ReDim aNew(1 To nr, 1 To nc)
    rNew = 0
    v = Chr(0)

    For r = 1 To nr
        tmp = a(r, COMPARE_COL)
        If tmp <> v Then
            rNew = rNew + 1
            For c = 1 To nc
                aNew(rNew, c) = a(r, c)
            Next c
            v = tmp
        End If
    Next r

    Selection.Value = aNew

End Sub

edited Jan 31, 2014 at 19:18

answered Jan 31, 2014 at 18:26

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

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

1 Comment

CongdonJL Over a year ago

Thanks! This seems to do what I need it to do!

hstay · Accepted Answer · 2014-01-31 15:56:31Z

0

Does this help?:

If data(i - 1, x) = data(i, x) Then
    data(i,x).EntireRow.Delete
End If

answered Jan 31, 2014 at 15:56

hstay

1,4391 gold badge11 silver badges20 bronze badges

4 Comments

CongdonJL Over a year ago

Getting a Subscript out of range

hstay Over a year ago

lastrow should be equal to (rangeEnd.row - rangeStart.row)

Tim Williams Over a year ago

@hstay - OP is not looping through a range (note there is no Set where they assign the range to data, so that line creates a 2-D array from the range values)

hstay Over a year ago

Thanks, @TimWilliams, I was blindfolded after seing Range().

brettdj · Accepted Answer · 2014-02-02 01:57:48Z

0

Why not use Excel's inbuilt Unique options (Data ... Remove Duplicates)?

Another efficient VBA method is to use a Dictionary.

Sub A_Unique_B()

Dim X
Dim objDict As Object
Dim lngRow As Long

Set objDict = CreateObject("Scripting.Dictionary")
X = Application.Transpose(Range([a1], Cells(Rows.Count, "A").End(xlUp)))

For lngRow = 1 To UBound(X, 1)
    objDict(X(lngRow)) = 1
Next

Range("B1:B" & objDict.Count) = Application.Transpose(objDict.Keys)
End Sub

answered Feb 2, 2014 at 1:57

brettdj

55.9k16 gold badges120 silver badges178 bronze badges

Collectives™ on Stack Overflow

Delete Row from Array

3 Answers 3

1 Comment

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related