VBA to stop blank lines being created in csv file

[jimmyaja123]

I have a VB code I created to save an excel file to a CSV format, however when created the CSV file adds on extra blank lines from formulas that are blank.

Is there a line of code which can stop this? and only include the data with the last available line of data? Below is the script I currently have:

Sub CSV()
If Sheets("File").Range("d16") = 0 Then
Sheets("1").Select
Range("A2").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.AutoFilter
ActiveSheet.Range("$A$1:$AH$600").AutoFilter Field:=1, Criteria1:="="
Rows("369:369").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Delete Shift:=xlUp
ActiveSheet.Range("$A$1:$AH$368").AutoFilter Field:=1
Selection.AutoFilter

Dim myCSVFileName As String
Dim tempWB As Workbook
Path1 = Worksheets("File").Range("C9")
Application.DisplayAlerts = False
On Error GoTo err
myCSVFileName = Worksheets("File").Range("C10")
ThisWorkbook.Sheets("1").Activate
ActiveSheet.Copy
Set tempWB = ActiveWorkbook
With tempWB
.SaveAs FileName:=myCSVFileName, FileFormat:=xlCSV, CreateBackup:=False
.Close
End With
err:
Application.DisplayAlerts = False

Sheets("1").Select
Range("A2").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Range(Selection, Selection.End(xlDown)).Select
Range("A2:AH600").Select
ActiveSheet.Paste
ActiveWorkbook.Save
Sheets("File").Select
Range("A1").Select

Else

MsgBox "Check raw data month, Macro did not run"
End If


End Sub

 

[XLfriend777]

Hello jimmyaja123 - Welcome to the forum. Hope this helps you get started. I'm sure there is some way to fix the code so that it doesn't include blank lines, but another approach might be to run this code that I found online that determines the last line with data and then removes blank rows above. I tried it on a small example and it worked. Good luck.

Sub DeleteAllEmptyRows()
    Dim LastRowIndex As Integer
    Dim RowIndex As Integer
    Dim UsedRng As Range
 
    Set UsedRng = ActiveSheet.UsedRange
    LastRowIndex = UsedRng.Row - 1 + UsedRng.Rows.Count
    Application.ScreenUpdating = False
 
    For RowIndex = LastRowIndex To 1 Step -1
        If Application.CountA(Rows(RowIndex)) = 0 Then
            Rows(RowIndex).Delete
        End If
    Next RowIndex
 
    Application.ScreenUpdating = True
End Sub