CODEWITHSUNDEEP
vbaexcel
Dyhouse
Dyhouse

Reputation: 83

Removing blank lines from a text file using VBA

This continues on from a previous question I have asked actually. I am desperate to find a way to remove the trailing blank lines from text files when generated from an excel file to which I have been unsuccessful so far. I have found the below code just now and when I execute it, I can see that it has the basis for what I want (I think) but I don't have the skill to amend it so that ignores any line with data in it and just deletes the blank spaces. Can anyone help me amend this so that it can delete those pesky white spaces please?

 Sub AltText()
  Dim File As String
  Dim VecFile() As String, Aux As String
  Dim i As Long, j As Long
  Dim SizeNewFile As Long

  File = Application.GetOpenFilename

  'Import file lines to array excluding first 3 lines and
  'lines starting with "-"
  Open File For Input As 1
  i = 0
  j = 0
  Do Until EOF(1)
    j = j + 1
    Line Input #1, Aux
    If j > 3 And InStr(1, Aux, "-") <> 1 Then
        i = i + 1
        ReDim Preserve VecFile(1 To i)
        VecFile(i) = Aux
    End If
  Loop
  Close #1
  SizeNewFile = i

  'Write array to file
  Open File For Output As 1
  For i = 1 To SizeNewFile
    Print #1, VecFile(i)
  Next i
  Close #1

  MsgBox "File alteration completed!"

 End Sub

Upvotes: 1

Views: 12048

Answers (2)

CodeSerendipity
CodeSerendipity

Reputation: 84

you need to look for blank spaces and carriage return characters, so after you read the line, check for content:

dim temp as string

temp = Replace (aux, chr(10), "")  
temp = Replace (temp,chr(13),"")
temp = Rtrim(Ltrim(temp))  ' remove just blank stuff

now check for the length:

if j > 3 and Len(temp) <> 0 then
    ......
    add the lines

so your code should look like this:

Sub AltText()
  Dim File As String
  Dim VecFile() As String, Aux As String
  Dim i As Long, j As Long
  Dim SizeNewFile As Long

  File = Application.GetOpenFilename

  'Import file lines to array excluding first 3 lines and
  'lines starting with "-"
  Open File For Input As 1
  i = 0
  j = 0
  Do Until EOF(1)
    j = j + 1
    Line Input #1, Aux

    '=====
     dim temp as string

     temp = Replace (aux, chr(10), "")  
     temp = Replace (temp,chr(13),"")
     temp = Rtrim(Ltrim(temp))  ' remove just blank stuff
    '======

    If j > 3 And Len(temp) <> 0 Then
        i = i + 1
        ReDim Preserve VecFile(1 To i)
        VecFile(i) = Aux
    End If
  Loop
  Close #1
  SizeNewFile = i

  'Write array to file
  Open File For Output As 1
  For i = 1 To SizeNewFile
    Print #1, VecFile(i)
  Next i
  Close #1

  MsgBox "File alteration completed!"

 End Sub

Upvotes: 2

Brian M Stafford
Brian M Stafford

Reputation: 8868

To remove lines that are blank, try the following code:

Sub AltText()
   Dim inFile As String
   Dim outFile As String
   Dim data As String

   inFile = Application.GetOpenFilename
   Open inFile For Input As #1

   outFile = inFile & ".alt"
   Open outFile For Output As #2

   Do Until EOF(1)
      Line Input #1, data

      If Trim(data) <> "" Then
         Print #2, data
      End If
   Loop

   Close #1
   Close #2

   Kill inFile
   Name outFile As inFile

   MsgBox "File alteration completed!"
End Sub

Upvotes: 3

Related Questions