How do I get VBA to skip cells with formulas that return blanks?

[ASanders]

I have a macro that creates a DAT (csv) file, and then prints out a header row, data rows and a footer row. I have forumlas in the DATA rows across all cells in the range (BX5 and across and down to the last column and row found - see code below). My questions is where I have an entire row of cells (formulas) not returning a value, how can i get VBA to ignore that row when printing to the DAT file? VBA code below:


Public Sub OutputQuotedCSV()
Const QSTR As String = """"
Dim myRecord As Range
Dim myField As Range
Dim nFileNum As Long
Dim sOut As String



nFileNum = FreeFile
Open "MOL_IOF_20101028_1548.DAT" For Output As #nFileNum

Print #nFileNum, Chr(34) & "HEAD" & Chr(34) & "," & Chr(34) & "ClientRef" & Chr(34) & "," & Chr(34) & "IOOF" & Chr(34) & "," & Chr(34) & "5" & Chr(34) & "," & Chr(34) & "201010260911" & Chr(34)

For Each myRecord In Range("BX5:BX" & _
Range("BX" & Rows.Count).End(xlUp).Row)


With myRecord
For Each myField In Range(.Cells(1), _
Cells(.Row, 256).End(xlToLeft))
sOut = sOut & "," & QSTR & _
Replace(myField.Text, QSTR, QSTR & QSTR) & QSTR
Next myField
Print #nFileNum, Chr(34) & "DATA" & Chr(34) & "," & Mid(sOut, 2)
sOut = Empty
End With
Next myRecord

Print #nFileNum, Chr(34) & "TAIL" & Chr(34) & "," & Chr(34) & "IOOF" & Chr(34) & "," & Chr(34) & "5" & Chr(34) & "," & Chr(34) & "1" & Chr(34)
Close #nFileNum

Const ForReading = 1
Const ForWriting = 2
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.OpenTextFile("MOL_IOF_20101028_1548.DAT", ForReading)
strFile = objFile.ReadAll
objFile.Close
intLength = Len(strFile)
strEnd = Right(strFile, 2)
If strEnd = vbCrLf Then
strFile = Left(strFile, intLength - 2)
Set objFile = objFSO.OpenTextFile("MOL_IOF_20101028_1548.DAT", ForWriting)
objFile.Write strFile
objFile.Close
End If

End Sub

 

[AlphaFrog]

Maybe try something like this (not tested)...

For Each myrecord In Range("BX5:BX" & _
Range("BX" & Rows.Count).End(xlUp).Row)

If Application.CountA(Range(myrecord, Cells(myrecord.Row, 255))) > 0 Then

With myrecord
For Each myField In Range(.Cells(1), _
Cells(.Row, 256).End(xlToLeft))
sOut = sOut & "," & QSTR & _
Replace(myField.Text, QSTR, QSTR & QSTR) & QSTR
Next myField
Print #nFileNum, Chr(34) & "DATA" & Chr(34) & "," & Mid(sOut, 2)
sOut = Empty
End With

End If
Next myrecord

 

[ASanders]

Thanks mate. Didnt work unfortunately.

 

[AlphaFrog]

For Each myrecord In Range("BX5:BX" & _
Range("BX" & Rows.Count).End(xlUp).Row)
With myrecord
[COLOR="Red"]For Each myField In Range(.Cells(1), Cells(.Row, 256).End(xlToLeft))
    sOut = sOut & myField.Text
Next myField
If Len(sOut) > 0 Then
sOut = vbNullString
[/COLOR]For Each myField In Range(.Cells(1), _
Cells(.Row, 256).End(xlToLeft))
sOut = sOut & "," & QSTR & _
Replace(myField.Text, QSTR, QSTR & QSTR) & QSTR
Next myField
Print #nFileNum, Chr(34) & "DATA" & Chr(34) & "," & Mid(sOut, 2)
sOut = vbNullString
End With

[COLOR="Red"]End If[/COLOR]
Next myrecord

 

[ASanders]

Awesome. Thanks for that! Just a note, your 'End If' needs to come before the 'End With':

For Each myrecord In Range("BX5:BX" & _
Range("BX" & Rows.Count).End(xlUp).Row)
With myrecord
For Each myField In Range(.Cells(1), Cells(.Row, 256).End(xlToLeft))
sOut = sOut & myField.Text
Next myField
If Len(sOut) > 0 Then
sOut = vbNullString
For Each myField In Range(.Cells(1), _
Cells(.Row, 256).End(xlToLeft))
sOut = sOut & "," & QSTR & _
Replace(myField.Text, QSTR, QSTR & QSTR) & QSTR
Next myField
Print #nFileNum, Chr(34) & "DATA" & Chr(34) & "," & Mid(sOut, 2)
sOut = vbNullString

End If
End With


Next myrecord

 

[AlphaFrog]

Glad it worked. Here's an after thought that might work as well...

For Each myrecord In Range("BX5:BX" & _
Range("BX" & Rows.Count).End(xlUp).Row)
With myrecord

For Each myField In Range(.Cells(1), _
Cells(.Row, 256).End(xlToLeft))
sOut = sOut & "," & QSTR & _
Replace(myField.Text, QSTR, QSTR & QSTR) & QSTR
Next myField
[COLOR="Red"]If Len(Replace(Replace(sOut, QSTR, ""), ",", "")) > 0 Then[/COLOR]
Print #nFileNum, Chr(34) & "DATA" & Chr(34) & "," & Mid(sOut, 2)
[COLOR="Red"]End If[/COLOR]
sOut = vbNullString
End With

Next myrecord