Hi,
I'm writing data into an Excel workbook, after writing the data I'll check for duplicates and remove them if found.
For this purpose, I'm using the Range.RemoveDuplicates in my project but want it to be more flexible (Excel 2016).
As you can see below, I have different cases and columns to check for duplicates.
Everything is now hardcoded in the project, but like to prevent this and define the Columns array as a variable.
I retire by the end of next year, and If a new case is needed, it can give a problem for people not familiar with VBA.
See my test code on the bottom and can't get it working ( Run-time error 13).
It doesn't matter If i define the ColumnNumbers As Variant or as String
If I can get this up and running, then It don't need to be hardcoded in the project, and can I place the column numbers into a configuration file (variable).
Can this be done or not, or do i need a complete different approach to obtain the same result? If so, how to do it?
To all: Merry Christmas & Happy 2022
Best regards,
Ludo
'Original code snipset
'********************************************
'remove eventual duplicate entries
'we don't take the columns 5,7 & 8 into account
'********************************************
Set rngDataRange = .Cells(1, 1).CurrentRegion
With rngDataRange
Select Case .Cells(1, 1).CurrentRegion.Columns.Count
Case 35
.RemoveDuplicates Columns:=Array(1, 2, 3, 4, 6, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35), Header:=xlYes
Case 36 '
.RemoveDuplicates Columns:=Array(1, 2, 3, 4, 6, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36), Header:=xlYes
Case 37
.RemoveDuplicates Columns:=Array(1, 2, 3, 4, 6, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37), Header:=xlYes
Case 38 '
.RemoveDuplicates Columns:=Array(1, 2, 3, 4, 6, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38), Header:=xlYes
Case 39
.RemoveDuplicates Columns:=Array(1, 2, 3, 4, 6, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39), Header:=xlYes
Case 45
.RemoveDuplicates Columns:=Array(1, 2, 3, 4, 6, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45), Header:=xlYes
End Select
End With
Display More
Here below is my test code generating a Run-time error 13
Option Explicit
'TEST CODE
Sub testDuplicates()
Dim rngDataRange As Range
Dim v2 As Variant
Dim ColumnNumbers As Variant 'String
ColumnNumbers = "1, 2, 3, 4, 6, 9, 10, 11, 12, 13, 14"
Set rngDataRange = ActiveSheet.Cells(1, 1).CurrentRegion
With rngDataRange
Select Case .Cells(1, 1).CurrentRegion.Columns.Count
Case 11
.RemoveDuplicates Columns:=Array(ColumnNumbers), Header:=xlYes '<< Run-time error 13
'
End Select
End With
End Sub
'*******************************************************************************
Display More