How to extract unique value from Filter in excel vba macro

Hello folks,

 

Welcome back...today will teach you "how to get unique values from set of values in excel using macro coding"

Open your excel file and create some duplicate data … (Don’t wait open and create now)

 

Here is my  duplicate data…



Now don’t wait for me, open VBA and do coding … (I can’t explain again the vba part so just visit How to write VBA coding  if you have any doubts)

If you are in developer mode then insert new module to do coding.

Paste the below coding’s on module (If you are a good Developer then you should do Copy Paste)

Sub UniqueValue()

Dim d As Object, c As Variant, i As Long, lr As Long

Set d = CreateObject("Scripting.Dictionary")

lr = ThisWorkbook.Worksheets(1).Cells(Rows.Count, 1).End(xlUp).Row

c = Range("A2:A" & lr)

For i = 1 To UBound(c, 1)

d(c(i, 1)) = 1

Next i

With ThisWorkbook.Worksheets(1)

.Activate

.Range("G1").Resize(d.Count) = Application.Transpose(d.Keys)

.Range("h1") = d.Count

End With

End Sub

Press F5 and Run

Gotcha! You got the final result

 

Now time to the coding part explanation …..If it is feel bore than I can’t do anything 

What is Sub? 
 
A Sub called procedure or a block of code, sub is executed in response to an event (Don’t ask what is Procedure? and what is Event? If you need explanation please comment)

Hint: Sub does not return any value.Sub UniqueValue()
End Sub



Variable Declaration:



Dim d As Object, c As Variant, i As Long, lr As Long

You can declare also like 

Dim d As Object
Dim c As variant 

Here I am going to set d object as a scripting dictionary ….Eeeeeeee don’t ask again what Script Dictionary ? check here my old post… (what is Script Dictionary?)



 d = CreateObject("Scripting.Dictionary") 

 Now, find last row of the particular column. (any one of these method)


lr = ThisWorkbook.Worksheets(1).Cells(Rows.Count, 1).End(xlUp).Row

 lr = ThisWorkbook.Worksheets("Sheet1").Cells(ThisWorkbook.Worksheets("Sheet1").Rows.Count, "A").End(xlUp).Row

lr =ThisWorkbook.Worksheets("Sheet1").UsedRange.Rows(ThisWorkbook.Worksheets("Sheet1").UsedRange.Rows.Count).Row

lr = ThisWorkbook.Worksheets("Sheet1").Range("A1").CurrentRegion.Rows.Count

okay, now some tricks…

We have a variable C as a variant data type. I am going to assign range of duplicate values in C

c = Range("A2:A" & lr)

Now use the below loop to assign unique values in dictionary object d . If you understand Scripting Dictionary then you will understand this loop.

For i = 1 To UBound(c, 1)

  d(c(i, 1)) = 1

Next i

 Reassign the last row and paste the value using transpose method

.Range("G2").Resize(d.Count) = Application.Transpose(d.Keys)

 


If you think above program is totally waste and then go with this

 
Use this coding ............

Public Sub Test()

ActiveSheet.Range("A2:A65536").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=ActiveSheet.Range("B2"), Unique:=True

End Sub

 

One line code will solve your problems. But it will work only in filter

What is Scripting.Dictionary? What is Array? in VBA excel macro

Hello all,

What is Scripting.Dictionary?

Nice question … don’t worry am here to answer. Before that What is Array?

Array is a data structure (Don’t ask what is data Structure) that contain group of element with the same data type.

Example:

Dim ArrayExample

ArrayExample=array(“Fail” ,”Mark”)


Or


ArrayExample(0)=”Fail”
ArrayExample(0)=”Mark”


(If you need to know about array and dimension comment below)


Now Dictionary :


Dictionary object is similar to PERL associative array…(Google it What is PERL array?)
Dictionary object can stored any value in the array and each stored items associated with a
unique key. We can retrieve data using key.( see the example then you will understand more)


Example:

Dim DictionaryExample   (Don’t write Dim DictionaryExample() )

Set  DictionaryExample = CreateObject(“Scripting.Dictionary”)

DictionaryExample.Add  “key”, “Fail”

DictionaryExample.Add  “keeey”, “Mark”


See the example now and read the explanation again.


(I know about your laziness read here) The value “Fail” and “Mark” stored in array, the stored items “Fail” and “Mark” associated with a unique key “key” and “keeey”. We can retrieve data “Fail” and “Mark” using key “key and Keeey”.


Program for array: (Do copy past like a programmer)

Sub ArrayExampleProgram()

Dim ArrayExample

ArrayExample = Array("Fail", "Mark")

Debug.Print ArrayExample(0)

Debug.Print ArrayExample(1)

End Sub

Sub ArrayExampleProgram1()

Dim ArrayExample(1)

ArrayExample(0) = "Fail"

ArrayExample(1) = "Mark"

Debug.Print ArrayExample(0)

Debug.Print ArrayExample(1)

End Sub 

Program for Data Dictionary:

Sub DictionaryExampleProgram()
Dim DictionaryExample
Set DictionaryExample = CreateObject("Scripting.Dictionary")


DictionaryExample.Add "key", "Fail"
DictionaryExample.Add "keeey", "Mark"


Debug.Print DictionaryExample.Item("key")
Debug.Print DictionaryExample.Item("keeey")
End Sub




(bye byeeeeeeeeee......)

 

How to write Macro coding? How to run macro coding ? How to see macro coding? How to Enable Developer Mode?

How to open VBA and run macro program in Excel

How to See Macro Coding?

Open New excel File …. And Press ALT + F11 it will redirect you to the coding window.

Else open Excel and find Developer mode and press Visual Basic.

Step: 1

How to enable Developer mode on excel?

OMG… if you can’t able to find Developer mode then go to File - > Options on your Excel And find Customize Ribbon and check Developer. Now do the above process to view the coding window.

Again Step 1:

Step 2:

How to Write Macro Coding? How to Run Macro Coding?

Click “ThisWorkbook” on project window (Left side )

And write your coding like below one (Don’t copy paste just 3 line you can type) ,Now press F5 to run macro coding.

Coding:

Sub Test()

Msgbox “Hello World”

End sub