VBA to stop blank lines being created in csv file

jimmyaja123

New Member
Joined
Jan 6, 2021
Messages
7
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
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

goesr

Well-known Member
Joined
Nov 15, 2013
Messages
662
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.

VBA Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,130,112
Messages
5,640,160
Members
417,128
Latest member
Xianter

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top