VBA help to output a file

[Paula Weill]

Hi, I need help in getting the following accomplished and would
appreciate any help you can give:

I have a spreadsheet that I want to write out to a file. In this
spreadsheet if there is a 0 in a certain cell then I don't want that
entire row written to the file but if there is an amount there I want
the entire row written out to the file.

Can anyone help? Thank you so much.

Paula

 

[JE McGimpsey]

one way (I assume that by "write out to a file" you mean a csv file, and
that the "certain cell" is in column A - adjust as necessary):

Public Sub CopyNonZeroToFile()
Const sFileName As String = "test.csv"
Dim rCrit As Range
With Application
.DisplayAlerts = False
.ScreenUpdating = False
End With
With ActiveSheet
Set rCrit = .Cells(1, .Columns.Count).End( _
xlToLeft).Offset(0, 2).Resize(2, 1)
rCrit(2).Formula = "=A2<>0"
Sheets.Add
.Range("A1").CurrentRegion.AdvancedFilter _
Action:=xlFilterCopy, _
CriteriaRange:=rCrit, _
CopyToRange:=ActiveSheet.Range("A1"), _
Unique:=False
rCrit.Clear
End With
ActiveSheet.Move
With ActiveWorkbook
.SaveAs Filename:=sFileName, _
FileFormat:=xlCSV, _
CreateBackup:=False
.Close SaveChanges:=False
End With
With Application
.DisplayAlerts = True
.ScreenUpdating = True
End With
End Sub

 

[Paula Weill]

When I run this macro I get the following error:

"Run time error 1004" "The exact range has a missing or illegal field
name"

The column that might have a zero in it is column F. And yes, I am
writing a .csv file. Thanks for any more info you can give!