• Latest
  • All
  • News
  • Business

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

Nov 12, 2016
Five cyber-security tools to look out for in 2022

Navigating the CVE Database – Tips for Efficient Vulnerability Research

Sep 26, 2023
How to Invest in Software and Security for Your Business

Unleashing Innovation: The Power of Custom Web Application Development

Sep 9, 2023
How to install and use a proxy for Instagram with OnlineProxy?

How to install and use a proxy for Instagram with OnlineProxy?

Sep 4, 2023
Unlocking the Power of Image Resizing in the Digital Era

Unlocking the Power of Image Resizing in the Digital Era

Sep 4, 2023
Top Benefits of Learning Salesforce for Beginners

Top Benefits of Learning Salesforce for Beginners

Sep 2, 2023

The Benefits of Implementing the SBG Bond Program in Your Organization

Sep 1, 2023
How Handheld Radios Improve Communication Efficiency in Industries

How Handheld Radios Improve Communication Efficiency in Industries

Sep 1, 2023
Impact and Importance of AI in Businesses

Revolutionizing Tech: Unveiling the Power of Specialized IT Solutions

Aug 29, 2023
Salesforce Training Best Practices: Strategies for Enhanced User Adoption

Salesforce Training Best Practices: Strategies for Enhanced User Adoption

Aug 29, 2023
Worldcoin (WLD) Price Today and Market Liquidity: Understanding Trading Volume

Worldcoin (WLD) Price Today and Market Liquidity: Understanding Trading Volume

Aug 29, 2023
Worldcoin (WLD) Price and Market Capitalization: Evaluating Token Value

Worldcoin (WLD) Price and Market Capitalization: Evaluating Token Value

Aug 10, 2023
How to Make Effective Business Videos for Your Marketing Campaign

Professional Finishing Techniques for Video Restoration

Aug 4, 2023
  • Guest Posts
  • Advertise
  • Contact Us
  • Privacy Policy
Tuesday, September 26, 2023
Tech Featured
  • Home
  • Latest Tech
  • Apple
  • How to’s
  • Apps
  • Automobiles
  • Gadgets
  • Gaming
  • Reviews
  • Tech Tips
No Result
View All Result
No Result
View All Result
Tech Featured
No Result
View All Result
Home News

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

by techfeatured
Nov 12, 2016
in News
0
1
SHARES
62
VIEWS
Share on FacebookShare on Twitter

[ad_1]

If you’ve ever worked with text files, you’ll know how frustrating it can be to remove empty lines. Part of the problem is that you don’t want to remove every blank line otherwise it might upset the formatting of the document.

This article will show you how to remove a set number of blank lines from a text file using some common sense VBA coding.

Opening And Reading The Text File

The logic of the code involves reading each line of the file, and saving the line into a text string if it meets our criteria. Then, we’ll write the text string back into a new file.

First, we’ll access the file system object (FSO) and open the file, which we’ve named “blanks.txt”. The code for accessing the file system is the sort of procedure you might want 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)

Removing The Blank Lines

Now, we can read each line in the file but first we need to consider the code we need to discard the blank lines.

We’ll start by defining several variables and give them initial values.

' Include the line in the new file?

Dim includeLine As Boolean

' The text string to write the included lines to
Dim allTxt as string

' The number of blank lines to include in the new file
Dim countBlanks As Long

countBlanks = 1
includeBlanks = 0
includeLine = False

The initial setting for blank lines is set at 1, so we don’t upset any paragraph breaks. If we wanted to remove every blank line we’d set the variable to 0.

Now we can read the file and tell the code which lines to write to the new file.

Do Until myFile.AtEndOfStream

includeLine = False

txt = myFile.ReadLine

The default for each line is to not include it in the new file unless it meets the criteria defined in the next few lines of code. The number of blank lines are counted until a non-blank line is found and then the counter is set back to zero.

  Select Case Len(txt)

Case 0

If countBlanks < includeBlanks Then includeLine = True

countBlanks = countBlanks + 1

Case Else

countBlanks = 0

includeLine = True

End Select

If the line meets our parameters, then we add it with a new line character to the txt string and continue the loop.

If includeLine Then

allTxt = allTxt & txt & vbCrLf
End If
Loop

With the reading of the text file completed, we close the file and write the text string to the new file.

myFile.close

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

With this type of file editing, it’s important not to overwrite the original file in case something unexpected occurs and you lose all the data. Even when you are sure the code is working correctly it makes sense to make a copy – using VBA – to safeguard the original data.

Summary

Because working with text files is a common occurrence for most Excel developers, it’s a good idea to develop relevant procedures that you can save for future reference – instead of searching for the answer when time is at a premium.

[ad_2]

Tags: androidapple iphonetechnology news
ShareTweetShareSendShare

For any queries, you can reach us at [email protected]

Latest Posts

  • Navigating the CVE Database – Tips for Efficient Vulnerability Research
  • Unleashing Innovation: The Power of Custom Web Application Development
  • How to install and use a proxy for Instagram with OnlineProxy?
  • Unlocking the Power of Image Resizing in the Digital Era
  • Top Benefits of Learning Salesforce for Beginners
Tech Featured

© 2022. All Rights Reserved.

Navigate Site

  • Guest Posts
  • Advertise
  • Contact Us
  • Privacy Policy

Follow Us

No Result
View All Result
  • Home
  • Latest Tech
  • Apple
  • How to’s
  • Apps
  • Automobiles
  • Gadgets
  • Gaming
  • Reviews
  • Tech Tips

© 2022. All Rights Reserved.