Writing non blank cell to text file

Question

So I have the following VBA code that writes to a CSV file, the problem is that the I have formula in a lot of the cells that return a blank value. The way the spreadsheet works will write put all of the valid results in the first rows leaving the rest blank. So I am trying to get

iLastRow = Range("A" & Rows.Count).End(xlUp).Row
iLastCol = Cells(1, Columns.Count).End(xlToLeft).Column

To count up to the last cell with data in as opposed to the last cell with a formula.

 Sub writeCSV()
    
    Dim iLastRow As Long
    Dim iLastCol As Long
    Dim FilePath As String
    Dim Filename As String
    Dim Fullpath As String
    
    Fullpath = Worksheets("GUI").Range("f10")
    
    iLastRow = Range("A" & Rows.Count).End(xlUp).Row
    iLastCol = Cells(1, Columns.Count).End(xlToLeft).Column
        Open Fullpath For Append As #1
            For i = 1 To iLastRow
                For j = 1 To iLastCol
                    If j <> iLastCol Then 'keep writing to same line
                        
                        Print #1, Cells(i, j),
                    Else 'end the line
                        Print #1, Cells(i, j)
                    End If
                Next j
            Next i
            'MsgBox "Failed to transfer " & iFail & " file(s).", iFail & " Transfer(s) Failed"
        Close #1
       
    End Sub

user_9814069 · Answer

Last Row and Column Using the Find Method

The Find method with its LookIn argument set to the xlValues parameter lets you ignore all blank cells, not only empty cells.

Sub LastRows()
    
    Dim lCell As Range
    ' Last Row
    Set lCell = Columns("A").Find("*", , xlValues, , , xlPrevious)
    If lCell Is Nothing Then Exit Sub ' empty first column range
    Dim iLastRow As Long: iLastRow = lCell.Row
    ' Last Column
    Set lCell = Rows(1).Find("*", , xlValues, , , xlPrevious)
    If lCell Is Nothing Then Exit Sub ' empty first row range
    Dim iLastCol As Long: iLastCol = lCell.Column
    
End Sub

user_11695049 · Answer

Since you said 'if data exists in one col it will exist in the other' you can exit the outer loop as soon as you encounter the first blank cell:

For i = 1 To iLastRow
    If Cells(i,1)="" then
      Print #1,  ' if you still need the line terminator
      exit for
    End If
    For j = 1 To iLastCol
    ...

Writing non blank cell to text file

2 Answers

Last Row and Column Using the Find Method