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,


    Here below is my test code generating a Run-time error 13

  • 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,


  • When using an array with RemoveDuplicates, it needs to be a zero based integer array.

    In the example file you posted there are only 11 columns, so the code snippet for this example would be:

    Obviously, you can expand this if your actual workbook has more columns but the principal will be the same.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!