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)
If relyBlanks < come withBlanks Then includeLine = True
relyBlanks = relyBlanks + 1
relyBlanks = zero
includeLine = True
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
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)
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.