Remove Duplicates with Dynamic Columns Array

trough

Board Regular
Joined
Oct 26, 2010
Messages
55
I am trying to write a subroutine to handle removal of duplicates in a dynamic fashion regardless of size of data and what columns are of interest to look for duplicates. It's pretty straight forward except I cannot figure out the dynamic specifying of the "Columns=:" parameter. It only works if I hardcode it as shown below (THE VERY LAST LINE BELOW). How can I use the string variable rmvLst = "1,3" shown below to specify the Array that goes into the Columns parameter? Or is there some way to dynamically specify the Array?

Code below shows the routine that generates the data (TestCall) and then calls the routine (ColRmv) that actually does the removal work. Here is data I use to test:

Capture.PNG


Option Explicit
Option Base 1

Sub TestCall()

Dim vData() As Variant
Dim Col2Rmv() As Long
Dim nRow, nCol, nRmv As Long

nRow = 10 'number data rows
nCol = 3 'number data columns
nRmv = 2 'number columns to consider

ReDim vData(1 To nRow, 1 To nCol)
ReDim Col2Rmv(1 To nRmv)

'populate columns of interest
Col2Rmv(1) = 1 'column to consider
Col2Rmv(2) = 3 'column to consider

'populate data
vData(1, 1) = "Richard"
vData(1, 2) = "California"
vData(1, 3) = "San Diego"

vData(2, 1) = "Brandon"
vData(2, 2) = "Florida"
vData(2, 3) = "Miami"

vData(3, 1) = "Gloria"
vData(3, 2) = "New York"
vData(3, 3) = "Rochester"

vData(4, 1) = "Brandon"
vData(4, 2) = "Texas"
vData(4, 3) = "Dallas"

vData(5, 1) = "Wesley"
vData(5, 2) = "Illinois"
vData(5, 3) = "Chicago"

vData(6, 1) = "Joseph"
vData(6, 2) = "New York"
vData(6, 3) = "Albany"

vData(7, 1) = "Tonya"
vData(7, 2) = "New York"
vData(7, 3) = "Rochester"

vData(8, 1) = "Joseph"
vData(8, 2) = "Texas"
vData(8, 3) = "Austin"

vData(9, 1) = "Gloria"
vData(9, 2) = "Florida"
vData(9, 3) = "Tampa"

vData(10, 1) = "Joseph"
vData(10, 2) = "New York"
vData(10, 3) = "Albany"

'call removal routine
Call ColRmv(vData, Col2Rmv)

End Sub

Sub ColRmv(vData() As Variant, Col2Rmv() As Long)

Dim WS As Worksheet 'temporary worksheet
Dim nRow, nCol, nRmv As Long
Dim rmvLst As String
Dim i, j As Integer

nRow = UBound(vData, 1)
nCol = UBound(vData, 2)
nRmv = UBound(Col2Rmv)

'create a new, temporary, sheet
Set WS = ThisWorkbook.Worksheets.Add

'copy data
For i = 1 To nRow
For j = 1 To nCol
WS.Cells(i, j) = vData(i, j)
Next j
Next i

'list of columns of interest
rmvLst = ""
For i = 1 To nRmv
If i = nRmv Then
rmvLst = rmvLst & Col2Rmv(i)
Else
rmvLst = rmvLst & Col2Rmv(i) & ","
End If
Next i

'remove duplicates (w/ Option Base 1 use VBA.Array)
WS.Range(Cells(1, 1), Cells(nRow, nCol)).RemoveDuplicates Columns:=VBA.Array(Col2Rmv(1), Col2Rmv(2))

End Sub
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

Forum statistics

Threads
1,215,076
Messages
6,122,988
Members
449,093
Latest member
Mr Hughes

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top