Delete Row from Array
16,838
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
Author by
CongdonJL
Updated on June 14, 2022Comments
-
CongdonJL almost 2 years
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
, andlastrow
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!
-
CongdonJL over 10 yearsGetting a Subscript out of range
-
hstay over 10 years
lastrow
should be equal to(rangeEnd.row - rangeStart.row)
-
Tim Williams over 10 years@hstay - OP is not looping through a range (note there is no
Set
where they assign the range todata
, so that line creates a 2-D array from the range values) -
CongdonJL over 10 yearsThanks! This seems to do what I need it to do!
-
hstay over 10 yearsThanks, @TimWilliams, I was blindfolded after seing Range().