How To Use VBA Code To Take away Blank Lines From A Textual content File

Posted in WordPress on Jul 3, 2014

If you’ve got at any time worked with text files, you are going to know how discouraging it can be to eliminate vacant lines. Element of the dilemma is that you never want to clear away every single blank line or else it may well upset the formatting of the document.

This write-up will clearly show you how to get rid of a established range of blank strains from a textual content file using some widespread feeling VBA coding.

Opening And Looking at The Textual content File

The logic of the code entails reading through every line of the file, and conserving the line into a textual content string if it fulfills our conditions. Then, we are going to produce the textual content string again into a new file.

First, we’ll access the file procedure item (FSO) and open the file, which we’ve named “blanks.txt”. The code for accessing the file procedure is the type of procedure you may want to conserve in a code library for upcoming reference.

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

filePath = ActiveWorkbook.path & “filesblanks.txt”
Established myFile = fso.openTextFile(filePath)

Taking away The Blank Strains

Now, we can study each individual line in the file but initial we need to have to contemplate the code we need to discard the blank strains.

We will start out by defining many variables and give them initial values.

‘ Incorporate the line in the new file?
Dim includeLine As Boolean

‘ The textual content string to compose the provided traces to
Dim allTxt as string

‘ The quantity of blank strains to involve in the new file
Dim countBlanks As Long

countBlanks = 1
includeBlanks =
includeLine = False

The preliminary placing for blank strains is set at one, so we don’t upset any paragraph breaks. If we preferred to get rid of just about every blank line we’d set the variable to .

Now we can browse the file and notify the code which strains to write to the new file.

Do Until eventually myFile.AtEndOfStream

includeLine = Untrue

txt = myFile.ReadLine

The default for each individual line is to not consist of it in the new file unless of course it fulfills the conditions described in the next number of strains of code. The range of blank traces are counted until a non-blank line is discovered and then the counter is established back again to zero.

Find Scenario Len(txt)


If countBlanks < includeBlanks Then includeLine = True

countBlanks = countBlanks + one

Scenario Else

countBlanks =

includeLine = Genuine

Close Choose

If the line satisfies our parameters, then we include it with a new line character to the txt string and carry on the loop.

If includeLine Then
allTxt = allTxt & txt & vbCrLf
Conclusion If

With the reading of the text file accomplished, we close the file and compose the textual content string to the new file.

filePath = ActiveWorkbook.route & “filesblanksRemoved.txt”
Established myFile = fso.CreateTextFile(filePath)
myFile.Compose allTxt
Set fso = Nothing at all

With this form of file enhancing, it really is vital not to overwrite the unique file in scenario a thing unanticipated happens and you shed all the info. Even when you are sure the code is operating effectively it makes feeling to make a copy – working with VBA – to safeguard the first knowledge.

Simply because doing the job with textual content data files is a widespread prevalence for most Excel builders, it is really a fantastic strategy to create pertinent treatments that you can help save for long term reference – instead of looking for the answer when time is at a quality.

By Andy L Gibson

Leave a Reply

Your email address will not be published. Required fields are marked *