Home » Word Press » How To Use VBA Code To Remove Blank Lines From A Text File

How To Use VBA Code To Remove Blank Lines From A Text File

If you will have ever labored with textual content recordsdata, you’ll be able to understand how irritating it may be to take away empty strains. Part of the issue is that you do not want to take away each and every clean line differently it could disappointed the formatting of the file.

This article will display you ways to take away a collection choice of clean strains from a textual content record the use of some commonplace sense VBA coding.

Opening And Reading The Text File

The good judgment of the code comes to studying each and every line of the record, and saving the road right into a textual content string if it meets our standards. Then, we will write the textual content string again into a brand new record.

First, we will get admission to the record machine object (FSO) and open the record, which we’ve got named “blanks.txt”. The code for having access to the record machine is such a process it’s possible you’ll wish to save in a code library for long run reference.

Dim fso As Scripting.FileDeviceObject

Set fso = New Scripting.FileDeviceObject
Dim myFile As Object
Dim filePath As String

filePath = ActiveWorkbook.trail & "filesblanks.txt"
Set myFile = fso.openTextFile(filePath)

Removing The Blank Lines

Now, we will learn each and every line within the record however first we want to believe the code we want to discard the clean strains.

We’ll get started via defining a number of variables and provides them preliminary values.

' Include the road within the new record?

Dim includeLine As Boolean

' The textual content string to put in writing the incorporated strains to
Dim allTxt as string

' The choice of clean strains to incorporate within the new record
Dim relyBlanks As Long

relyBlanks = 1
come withBlanks = zero
includeLine = False

The preliminary atmosphere for clean strains is about at 1, so we do not disappointed any paragraph breaks. If we would have liked to take away each and every clean line we might set the variable to zero.

Now we will learn the record and inform the code which strains to put in writing to the brand new record.

Do Until myFile.AtEndOfStream

includeLine = False

txt = myFile.ReadLine

The default for each and every line is not to come with it within the new record except it meets the factors outlined in the following couple of strains of code. The choice of clean strains are counted till a non-blank line is located after which the counter is about again to 0.

  Select Case Len(txt)

Case zero

If relyBlanks < come withBlanks Then includeLine = True

relyBlanks = relyBlanks + 1

Case Else

relyBlanks = zero

includeLine = True

End Select

If the road meets our parameters, then we upload it with a brand new line personality to the txt string and proceed the loop.

If includeLine Then

allTxt = allTxt & txt & vbCrLf
End If

With the studying of the textual content record finished, we shut the record and write the textual content string to the brand new record.


filePath = ActiveWorkbook.trail & "filesblanksRemoved.txt"
Set myFile = fso.CreateTextFile(filePath)
myFile.Write allTxt
Set fso = Nothing

With this sort of record enhancing, it can be crucial to not overwrite the unique record in case one thing sudden happens and also you lose the entire knowledge. Even if you find yourself positive the code is operating appropriately it is sensible to keep a copy – the use of VBA – to safeguard the unique knowledge.


Because running with textual content recordsdata is a commonplace prevalence for many Excel builders, it is a good suggestion to increase related procedures that you’ll save for long run reference – as an alternative of attempting to find the solution when time is at a top rate.

Check Also

Santa Claus Game   Unity Android Game Template (Games)

Santa Claus Game – Unity Android Game Template (Games)

Santa Claus Game – Unity Android Game Template (Games). Build your individual Santa Claus Game. …