How to show unique values of Column B from Multiple sheets using a function

K

Kandavalli.Kiran

New member
Joined
Dec 18, 2014
Messages
3
Reaction score
0
Points
0
Hi Everyone,

Can I know how to show unique values of column B from Multiple sheets using a function. Please suggest!

Thanks!
Kiran
 
Thank you!

But I am not getting the correct result. Could you please help me on the logic.

I need to ignore first two sheet and from the third sheet I need to show the distinct values based on Column B (Range is B3 to B1000) for the remaining sheets.
 
Try altering it to:

Code: 
Sub UniqueValues()
Dim newWS As Worksheet, r As Long, N As Long, i As Integer
Application.ScreenUpdating = False

For Each ws In Sheets
    Application.DisplayAlerts = False
    If ws.Name = "UNIQUE_DATA" Then ws.Delete
    Application.DisplayAlerts = True
Next

Set newWS = Sheets.Add(after:=Sheets(Sheets.Count))
newWS.Name = "UNIQUE_DATA"
N = 1
For i = 3 To Sheets.Count - 1
    r = Sheets(i).Cells(Rows.Count, "B").End(xlUp).Row
    Sheets(i).Range("B1:B" & r).Copy
    Cells(N, 2).PasteSpecial xlValues
    N = Cells(Rows.Count, "B").End(xlUp).Row + 1
Next
r = Cells(Rows.Count, "B").End(xlUp).Row
Debug.Print r
Range("B1:B" & r).AdvancedFilter _
Action:=xlFilterInPlace, Unique:=True
Range("B1:B" & r).Copy
Range("A1").PasteSpecial xlValues

Application.CutCopyMode = False
Range("B1:B" & r).AdvancedFilter _
Action:=xlFilterInPlace, Unique:=False
Columns(2).Delete
r = Cells(Rows.Count, "A").End(xlUp).Row
Range("A1:A" & r).Sort key1:=Range("A1"), Header:=xlNo
Application.ScreenUpdating = True
End Sub
 
You must log in or register to reply here.
Back
Top