How To Use VBA Code To Remove Blank Lines From A Text File
If you happen to’ve ever labored with textual content information, you will know the way irritating it may be to take away empty strains. A part of the issue is that you do not need to take away each clean line in any other case it’d upset the formatting of the doc.
This text will present you the right way to take away a set variety of clean strains from a textual content file utilizing some widespread sense VBA coding.
Opening And Studying The Textual content File
The logic of the code includes studying every line of the file, and saving the road right into a textual content string if it meets our standards. Then, we’ll write the textual content string again into a brand new file.
First, we’ll entry the file system object (FSO) and open the file, which we have named “blanks.txt”. The code for accessing the file system is the kind of process you would possibly need to save in a code library for future reference.
Dim fso As Scripting.FileSystemObject
Set fso = New Scripting.FileSystemObject
Dim myFile As Object
Dim filePath As String
filePath = ActiveWorkbook.path & “filesblanks.txt”
Set myFile = fso.openTextFile(filePath)
Eradicating The Clean Strains
Now, we are able to learn every line within the file however first we have to take into account the code we have to discard the clean strains.
We’ll begin by defining a number of variables and provides them preliminary values.
‘ Embrace the road within the new file?
Dim includeLine As Boolean
‘ The textual content string to jot down the included strains to
Dim allTxt as string
‘ The variety of clean strains to incorporate within the new file
Dim countBlanks As Lengthy
countBlanks = 1
includeBlanks = zero
includeLine = False
The preliminary setting for clean strains is about at 1, so we do not upset any paragraph breaks. If we wished to take away each clean line we would set the variable to zero.
Now we are able to learn the file and inform the code which strains to jot down to the brand new file.
Do Till myFile.AtEndOfStream
includeLine = False
txt = myFile.ReadLine
The default for every line is to not embody it within the new file until it meets the standards outlined within the subsequent few strains of code. The variety of clean strains are counted till a non-blank line is discovered after which the counter is about again to zero.
Choose Case Len(txt)
Case zero
If countBlanks < includeBlanks Then includeLine = True
countBlanks = countBlanks + 1
Case Else
countBlanks = zero
includeLine = True
Finish Choose
If the road meets our parameters, then we add it with a brand new line character to the txt string and proceed the loop.
If includeLine Then
allTxt = allTxt & txt & vbCrLf
Finish If
Loop
With the studying of the textual content file accomplished, we shut the file and write the textual content string to the brand new file.
myFile.shut
filePath = ActiveWorkbook.path & “filesblanksRemoved.txt”
Set myFile = fso.CreateTextFile(filePath)
myFile.Write allTxt
myFile.Shut
Set fso = Nothing
With this kind of file modifying, it is essential to not overwrite the unique file in case one thing sudden happens and also you lose all the information. Even when you find yourself positive the code is working accurately it is sensible to make a replica – utilizing VBA – to safeguard the unique information.
Abstract
As a result of working with textual content information is a standard prevalence for many Excel builders, it is a good suggestion to develop related procedures which you could save for future reference – as an alternative of trying to find the reply when time is at a premium.