Making a notepad text file from select spreadsheet cells but eliminating empty cells causing spaces in the text file.

[jaystub]

I'm currently using a VBA macro to gather cell data and transfer the data to a notepad text file. My macro works great but unfortunately if a cell is empty the notepad will add space to the text file. How can I eliminate the empty cells causing spaces in the text file?

My current macro is as follows:

Sub data_out_save_S1414()
Dim iCntr
Dim strFile_Path As String
strFile_Path = Range("fo1")
Open strFile_Path For Output As #1
For iCntr = 5 To 1514
Print #1 , Range("h" & iCntr)
Next iCntr
Close #1
CreateObject("WScript.Shell").Popup "Your data is now saved as a NotePad file.", 3
End Sub


Thank you in advance for your ideals or suggestions.

"Bubba"

 

[Norie]

Try this.

Sub data_out_save_S1414()
Dim iCntr As Long
Dim strFile_Path As String

    strFile_Path = Range("fo1")
    
    Open strFile_Path For Output As #1 
        For iCntr = 5 To 1514
            If Range("H" & iCntr).Value <> "" Then
                Print #1 , Range("h" & iCntr)
            End If
        Next iCntr
    Close #1 
    
    CreateObject("WScript.Shell").Popup "Your data is now saved as a NotePad file.", 3
    
End Sub

 

[jaystub]

Norie,
Thank you for your help. Your application helped me understand my mistake on this. I modified your line below:

If Range("H" & iCntr).Value <> "" Then

to

If Range("H" & iCntr).Value <> " " Then

It was not working until I added the space between the quotes.

Many thanks for your assistance.

Sincerely,
"Bubba"