Remove Duplicates from range of cells in excel vba

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

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

'*******************************************************************************

Quote from royUK

An example workbook would help. Which line errors?

It doesn't make sense declaring a variable for the CurrentRegion.

Hi Roy

Find an example workbook in the attachement.

It fails in following line:

.RemoveDuplicates Columns:=Array(ColumnNumbers), Header:=xlYes '<< Run-time error 13

Is also noted in my first question, last code example.

I use the currentregion to determine how many columns I have in the data file and use this number in the Case statement.

More info:

I work for the aviation bussiness and every cocpit monitor goes into a climate chamber where a temperature, power on/off & vibration is applied using a profile and creates a log file.

During this profele time (typical 4hours), a test program runs and create a log file.

After the profile is applied, I readout the minimum & maximum temperartures from the climate chamber log file AND the different temperatures from the test program.

Because every TYPE of monitor is different, the number of temperature sensors in the cocpit monitor is also variable.

Thats what you can see in my initial question, the upper code snipset. (35,36,37,38,39 & 45 columns).

The idea is to place those column numbers into the TYPE monitor configuration file so that the VBA code could be generic for any number of columns.

making it a lot easyer to configure this way than changing the VBA code.

Anyhow, thanks for looking at it.

best regards,

Ludo

Thanks Gijsmo, it does the trick.

Merry Christmas & Happy 2022

Best regards,

Ludo