vba - 如何在不导出 Visual Basic Excel 中的公式的情况下导出已使用的值范围

Question

我尝试了多种变体，并不断得到相同的结果。最近的迭代是这样的:

Sub CopyToCSV()

' Copy and Paste Active Sheet
ActiveSheet.Copy
With ActiveSheet.UsedRange
    .Value = .Value
End With

 'The new workbook becomes Activeworkbook:
With ActiveWorkbook


      'Saves the new workbook to given folder / filename:
     .SaveAs Filename:= _
     "C:\upload\19meat-kl.csv", _
     FileFormat:=xlCSV, _
     CreateBackup:=False
      'Closes the file
     .Close False
  End With
End Sub

我的输出看起来像这样:

Store,Department,Date,Category,Item Count,Retail Price,Dollars,Reduced Retail,Total Loss,Reason for loss
18,90,2017-04-04,Meat,2,3,6,1,4,Out of Date
18,90,2017-04-04,Pork,2,1.5,3,0.99,1.02,Other
,,,,,,,,,
,,,,,,,,,
,,,,,,,,,
,,,,,,,,,
,,,,,,,,,

原因是我的 Excel 文件包含一些这样的公式:

=IF(D5>0,TODAY(),"")

如果它们在某些单元格中输入任何内容，则此自动填充某些单元格。这会导致 Excel 认为单元格已“使用”并包含它们。我尝试了 Worksheet.usedrange.values 的几种变体，要么得到相同的输出，要么由于代码错误而得到调试提示。如何从输出中删除逗号？

Answer 1

已编辑 包括整个代码

只需清除几乎空白的单元格

Sub CopyToCSV()
    ' Copy and Paste Active Sheet
    ActiveSheet.Copy
    With ActiveSheet.UsedRange
        .Value = .Value
        .SpecialCells(xlCellTypeBlanks).EntireRow.Delete
    End With

    'The new workbook becomes Activeworkbook:
    With ActiveWorkbook
        'Saves the new workbook to given folder / filename:
        .SaveAs Filename:="C:\upload\19meat-kl.csv", FileFormat:=xlCSV, CreateBackup:=False
        'Closes the file and discard changes (no needed any more)
        .Close False
    End With
End Sub