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:
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
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:
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