In this article, we will create a macro to extract unique values from the defined range.
Raw data for this example consists of duplicate entries of country names in the range A7:A21.
We have created “FindUniqueValues” macro to extract unique values from the defined range. This macro can be run by clicking the “Submit” button. Before clicking the “Submit” button, we have to specify the address of the range which contains duplicate data in the cell H9 and address of the destination where output should appear.
“FindUniqueValues” macro takes two range objects as input parameters. First parameter defines the range which contains the duplicate data and second parameter defines the position of the starting cell which will contain the output. This macro cannot be called directly as we have to specify the parameters, so we have created second macro “MacroRunning” to call the macro.
“MacroRunning” macro calls the “FindUniqueValues” macro with the parameter input by the user.
SourceRange.AdvancedFilter Action:=xlFilterCopy, _
AdvancedFilter method of Range object is used to filter or copy data from the range based on a certain criteria. To copy only the unique values, we have to set Unique parameter of AdvancedFilter to True.
Please follow below for the code
Option Explicit Sub FindUniqueValues(SourceRange As Range, TargetCell As Range) 'Using advance filter for extacting unique items in the source range SourceRange.AdvancedFilter Action:=xlFilterCopy, _ CopyToRange:=TargetCell, Unique:=True End Sub Sub MacroRunning() 'Calling FindUniqueValues macro Call FindUniqueValues(Range(Range("H9").Value), Range(Range("H10").Value)) End Sub
We would love to hear from you, do let us know how we can improve our work and make it better for you. Write to us at firstname.lastname@example.org
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.