excel - 将非空白单元格写入文本文件

Question

所以我有以下写入 CSV 文件的 VBA 代码，问题是我在许多返回空白值的单元格中有公式。电子表格的工作方式会将所有有效结果放在第一行，其余为空白。所以我想得到

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

计数到最后一个带有数据的单元格，而不是最后一个带有公式的单元格。

 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

Answer 1

使用 Find 方法的最后一行和最后一列

Find方法及其 LookIn参数设置为 xlValues参数允许您忽略所有空白单元格，而不仅仅是空单元格。

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