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 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.

No Comments

Post a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.