Read text File into listbox in reverse order

yinkajewole

Active Member
Joined
Nov 23, 2018
Messages
281
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
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
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
 
Upvote 0
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?
 
Upvote 0
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:
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
Upvote 0

Forum statistics

Threads
1,213,544
Messages
6,114,239
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top