-3

My scenario is that

How to print Unique values one by one in text file from column in excel using vba and also show value in msgbox one by one using vba Please give me a suggestion

thanks in Advance

Amruta Raut
  • 53
  • 8
  • Use a dictionary to collect unique values and loop over that writing to file and msgbox. – QHarr Apr 20 '18 at 09:45
  • @ QHarr thank you for response ,Can you please more elaborate it – Amruta Raut Apr 20 '18 at 09:56
  • Hi, welcome to SO. There are loads of google results and SO examples to help you with this. For starters: https://www.mrexcel.com/forum/excel-questions/817492-extract-unique-values-one-column-using-dictionary.html and https://stackoverflow.com/questions/23294634/write-vba-dictionary-to-text-file?utm_medium=organic&utm_source=google_rich_qa&utm_campaign=google_rich_qa – QHarr Apr 20 '18 at 09:57

2 Answers2

0

here's the "msgbox" part:

Sub main()
    Dim cell As Range

    For Each cell In Columns(1).SpecialCells(xlCellTypeConstants, xlTextValues) 'change column index to suit your need
        If WorksheetFunction.CountIf(Range(Cells(1, cell.Column), cell), cell.Value) < 2 Then MsgBox cell.Value
    Next
End Sub

I'll leave the "print to text file" part to you

DisplayName
  • 13,283
  • 2
  • 11
  • 19
0
Sub Unik()
Dim sh As Worksheet, sh2 As Worksheet, lr As Long, rng As Range
Set sh = Sheet1 'Edit sheet name
Dim strFile_Path As String
Dim i As Integer
 Dim iCntr As Long
 Close #1
    Open "C:\Users\Downloads\ghds.txt" For Output As #1
lr = sh.Cells(Rows.Count, 1).End(xlUp).Row
Set rng = sh.Range("A2:A" & lr)
rng.AdvancedFilter Action:=xlFilterInPlace, Unique:=True
sh.Rows(1).Hidden = True
For Each c In rng.SpecialCells(xlCellTypeVisible)
If Mystr = "" Then
Mystr = c.Value
Else
Mystr = Mystr & ", " & c.Value
Print #1, c.Value
End If
Next
Close #1
End Sub
Amruta Raut
  • 53
  • 8