Read text File into listbox in reverse order

yinkajewole

Board Regular
I have some lines in a text file. the lines were written in a way such that the last line is added at the bottom. i have a listbox that i want it to read the lines in the text file in a reverse order i.e from the last line to the first line
 

Rick Rothstein

MrExcel MVP
I have some lines in a text file. the lines were written in a way such that the last line is added at the bottom. i have a listbox that i want it to read the lines in the text file in a reverse order i.e from the last line to the first line
Assuming your ListBox is on a UserForm and that it is named ListBox1 and that you change the red highlighted text to your actual path/filename, run this subroutine from whatever event (initialize, button click, etc.) you want...
Code:
Sub ReverseTextFileShowInListBox()
  Dim X As Long, FileNum As Long, TotalFile As String, Lines As Variant
  FileNum = FreeFile
  Open "[B][COLOR="#FF0000"]C:\Temp\ReverseMe.txt[/COLOR][/B]" For Binary As [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FileNum]#FileNum[/URL] 
    TotalFile = Space(LOF(FileNum))
    Get [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FileNum]#FileNum[/URL] , , TotalFile
  Close [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FileNum]#FileNum[/URL] 
  Lines = Split(TotalFile, vbCrLf)
  For X = UBound(Lines) To 0 Step -1
    ListBox1.AddItem Lines(X)
  Next
End Sub
 

yinkajewole

Board Regular
I'm not with my pc at the moment, i'll try it later.
by the way, is the possible to list them by eliminating duplicates?
 

Rick Rothstein

MrExcel MVP
I'm not with my pc at the moment, i'll try it later.
by the way, is the possible to list them by eliminating duplicates?
That will take different code as this is basically a different question. However, you need to clarify something for us. Since you are asking for the list to be reversed, the question comes to mind which duplicate should be kept... the first one on the existing list or the last one? For example, if this is your list...

nine
eight
three
seven
six
three
three
five
four
three
two
one

would you want the reversed list to be this...

one
two
four
five
six
seven
three
eight
nine

or this...

one
two
three
four
five
six
seven
either
nine
 
Last edited:

yinkajewole

Board Regular
the last one on the existing list i.e
jack
john
peter
lord
john --> this should be retained
cole
 

Rick Rothstein

MrExcel MVP
the list should be reversed like this...
one
two
four
five
six
seven
three
eight
nine
Try using this code in place of the code I gave you earlier...
Code:
Sub ReverseTextFileShowInListBox()
  Dim X As Long, FileNum As Long
  Dim TotalFile As String, InListBox As String
  Dim Lines As Variant
  FileNum = FreeFile
  Open "C:\Temp\ReverseMe.txt" For Binary As [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FileNum]#FileNum[/URL] 
    TotalFile = Space(LOF(FileNum))
    Get [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FileNum]#FileNum[/URL] , , TotalFile
  Close [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FileNum]#FileNum[/URL] 
  Lines = Split(TotalFile, vbCrLf)
  With CreateObject("Scripting.Dictionary")
    For X = UBound(Lines) To 0 Step -1
      If .Exists(Lines(X)) Then .Remove Lines(X)
      .Item(Lines(X)) = 1
    Next
    ListBox1.List = .Keys
  End With
End Sub
 

DanteAmor

Well-known Member
Only another way

Code:
Private Sub CommandButton1_Click()
  Workbooks.OpenText Filename:="[COLOR=#ff0000]C:\trabajo\otro.txt[/COLOR]", Origin:=xlWindows, FieldInfo:=Array(1, 1)
  Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row).RemoveDuplicates Columns:=1, Header:=xlYes
  Range("B1:B" & Range("A" & Rows.Count).End(xlUp).Row) = "=INDIRECT(""A"" & " & Range("A" & Rows.Count).End(xlUp).Row + 1 & "-ROW())"
  ListBox1.List = Range("B1:B" & Range("A" & Rows.Count).End(xlUp).Row).Value
  ActiveWorkbook.Close False
End Sub
 

yinkajewole

Board Regular
Try using this code in place of the code I gave you earlier...
Code:
Sub ReverseTextFileShowInListBox()
  Dim X As Long, FileNum As Long
  Dim TotalFile As String, InListBox As String
  Dim Lines As Variant
  FileNum = FreeFile
  Open "C:\Temp\ReverseMe.txt" For Binary As [URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FileNum"]#FileNum[/URL] 
    TotalFile = Space(LOF(FileNum))
    Get [URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FileNum"]#FileNum[/URL] , , TotalFile
  Close [URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FileNum"]#FileNum[/URL] 
  Lines = Split(TotalFile, vbCrLf)
  With CreateObject("Scripting.Dictionary")
    For X = UBound(Lines) To 0 Step -1
      If .Exists(Lines(X)) Then .Remove Lines(X)
      .Item(Lines(X)) = 1
    Next
    ListBox1.List = .Keys
  End With
End Sub
great it worked
 

yinkajewole

Board Regular

Some videos you may like

This Week's Hot Topics

  • Get External Data (long shot question!)
    This is likely a long shot but I am wondering if it is at all possible for Excel to somehow 'change' the contents of a URL that is being linked to...
  • Importing multiple excel files into one spreadsheet
    Hi, I'm trying to import multiple excel files (with the same format into a single spreadsheet) so that each day's file is listed underneath the...
  • Cell Formatting
    Good Morning, I need to format a few different cells in the following manners: A1 has to always add a colon (:) after whatever is typed in by a...
  • How to copy multiple rows using If
    Hi all, I'm very new to VBA and have written this simple code to copy certain cells if a certain cell within that row contains any data. I need...
  • Workbook_Change stopped working !
    I am working on an app to speed up & automate processing of Credit Cards statements. After data is input from a CSV file, it is presented to the...
  • VBA If statement
    Dear All, I have two dates, where I'd like a message box to pop, if the dates are between this criteria. [CODE] sDate1 = #10/1/2019#...
Top