Need some help please - Macro to create a CSV file and remove blank rows.

Good Morning to you all.

I have the existing VBA code (see below) to create a CSV file from a excel sheet and this work well, however the problem is the software that reads the CSV file does not like data missing in rows or enire blank rows.

Therefore I need a macro that would amend the new CSV file as it is been created to remove entire rows when data is not present in column A.

Is this possible?

Sub CopyToCSV()
Dim MyPath As String
Dim MyFileName As String
'The path and file names:
MyPath = "C:\V10\demo\import\"
MyFileName = Sheets(1).Cells(1, 1).Value
'Makes sure the path name ends with "\":
If Not Right(MyPath, 1) = "\" Then MyPath = MyPath & "\"
'Makes sure the filename ends with ".csv"
If Not Right(MyFileName, 4) = ".csv" Then MyFileName = MyFileName & ".csv"
'Copies the sheet to a new workbook:
Sheets("upload").Copy
'The new workbook becomes Activeworkbook:
With ActiveWorkbook
'Saves the new workbook to given folder / filename:
.SaveAs Filename:= _
MyPath & MyFileName, _
FileFormat:=xlCSV, _
CreateBackup:=False
'Closes the file
.Close False
End With
End Sub

Thanks in anticipation.

Stuart

How aboth setting an autofilter on the excel file and setting the "Field" as 1 and "Criteria1 as "<>" to get rid of the blank rows, something like

Option Explicit

Sub AutFilt()
Range("A1:D8").AutoFilter Field:=1, Criteria1:="<>"
End Sub

and the copy the filterd rang using the command

ActiveSheet.AutoFilter.Range.Copy

and the make a CSV file of the copied range.

Alf

Thanks for the reply but this doesn't seem to work.

Attached is the spreadhseet I have so far with a marco that isn't now working, any help would be appreciated?

Thanks

You could try this macro instead as you have both blanks and zero values in column A

Sub NoBlankOrZero()
Dim i As Long

i = Range("A" & Rows.Count).End(xlUp).Row

Range("A1:J" & i).AutoFilter Field:=1, Criteria1:="<>", Operator:=xlAnd, Criteria2:="<>0"

' ActiveSheet.AutoFilter.Range.Copy

End Sub

I set the autofilter range based on last value in column A but found later out that there were more rows i column i and j with value i.e. "#Ref!" so you either use column i or j for setting the autofilte range or you delete those two last rows so that A column has the same number of rows as i and j column.

I've inactivated copy part with the ' since I think you should copy and paste this to a sheet in you file so you can have a look at the result first to see if you can convert it to a csv file that you software can read without any problems.

Alf