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

Question

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

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

Answer 2

既然您说过'如果数据存在于一个颜色中，那么它将存在于另一个'，那么您可以在遇到第一个空白单元格时立即退出外部循环:

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
...