Export to CSV format

[Gestron]

Dear All,

I'm using Office 97, I've 2 macros : a) hide the rows with amount valu
zero and b) automatic save the sheet to local drive with
csv-extension. But, when the sheet is saved I noticed that in the ne
file the hidden rows are still visual. How to avoid these lines ????

Used code (a) Hidden rows :

Sub hide_rows()
Dim RowNdx As Long
Dim LastRow As Long

LastRow = ActiveSheet.Cells(Rows.Count, "W").End(xlUp).Row
For RowNdx = LastRow To 1 Step -1
If Cells(RowNdx, "W").Value = 0 Then
Rows(RowNdx).Hidden = True
End If
Next RowNdx
End Sub

Used code (b) save to csv :

Sub export_csv()

ActiveSheet.Copy
ActiveWorkbook.SaveAs FileName:="C:\Temp\Upload.csv"
FileFormat:=xlCSV, CreateBackup:=False
ActiveWorkbook.Close SaveChanges:=Fals

 

[Ron de Bruin]

You can try this

It will copy only the visible cells as values (activesheet) to a new workbook
and save that workbook as a csv file

Sub copy_ActiveSheet()
Dim wb As Workbook
Dim source As Range
Dim dest As Workbook

Set source = Nothing
On Error Resume Next
Set source = Cells.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If source Is Nothing Then
MsgBox "The source is not a range or the sheet is protect, please correct and try again.", vbOKOnly
Exit Sub
End If

Application.ScreenUpdating = False
Set dest = Workbooks.Add(xlWBATWorksheet)
source.Copy
With dest.Sheets(1)
.Cells(1).PasteSpecial Paste:=8
' Paste:=8 will copy the column width in Excel 2000 and higher
.Cells(1).PasteSpecial xlPasteValues, , False, False
.Cells(1).PasteSpecial xlPasteFormats, , False, False
.Cells(1).Select
Application.CutCopyMode = False
End With

dest.SaveAs Filename:="C:\Upload.csv", _
FileFormat:=xlCSV, CreateBackup:=False
dest.Close SaveChanges:=False

Application.ScreenUpdating = True
End Sub