The MessageBox should not show HEADER1, it should display only the desired / expected result (well done here above) and also, such finding should not be be filled with color in respective Cells in respective column.
Also, if the finding / outcome is more than 20 row items, will messagebox adjust itself in those cases ?
As a VBA starter demonstration just using Excel Basics like any beginner operating manually :
PHP Code:
Sub Demo1() Dim V Sheet2.UsedRange.Clear With Sheet1.Range("B11:B" & Sheet1.UsedRange.Rows.Count) V = "=COUNTIF(" & .Address(External:=True) & ",A1)=1" .AdvancedFilter xlFilterCopy, , Sheet2.[A1], True End With With Sheet2.UsedRange.Columns("A:B") .Item(2).Formula = V .Cells(2).Clear .Sort .Cells(2), xlAscending, Header:=xlYes If Not .Range("B2") Then V = .Item(2).Find(False, , xlValues, xlWhole, , xlPrevious).Row MsgBox Join(.Parent.Evaluate("TRANSPOSE(A2:A" & V & ")"), vbLf) End If End With End Sub
Do you like it ? So thanks to click on bottom left star icon « ★ Add Reputation » !
Fast execution, but i want to flash through MessageBox only Unique Values from/amongst DUPLICATES (So, idea is to have first duplicates, and then flash only each unique item from them).
As a VBA starter demonstration just using Excel Basics like any beginner operating manually :
PHP Code:
Sub Demo1() Dim V Sheet2.UsedRange.Clear With Sheet1.Range("B11:B" & Sheet1.UsedRange.Rows.Count) V = "=COUNTIF(" & .Address(External:=True) & ",A1)=1" .AdvancedFilter xlFilterCopy, , Sheet2.[A1], True End With With Sheet2.UsedRange.Columns("A:B") .Item(2).Formula = V .Cells(2).Clear .Sort .Cells(2), xlAscending, Header:=xlYes If Not .Range("B2") Then V = .Item(2).Find(False, , xlValues, xlWhole, , xlPrevious).Row MsgBox Join(.Parent.Evaluate("TRANSPOSE(A2:A" & V & ")"), vbLf) End If End With End Sub
Do you like it ? So thanks to click on bottom left star icon « ★ Add Reputation » !
Bookmarks