Save to range up to cells with "" value

[Anvar]

Hello All

Newbie here. I have a project in excel which I patched up using very helpful threads on this forum. Basically the idea is to save data from a sheet on to a new csv file. The code is further below. My issue is that due to a formula present in the cells (IFNA return "") the result is that the new CSV appears to contain data on resulting csv file.

I tried numerous ways to only save the range to CSV where there is "real" data in rows (say column B).

Could you please help me to get rid of ,,,,,,,,,,,,,,,,,,,,,,,,,, which appear in empty cells if file is opened with a notepad?

Please let me know if any of tthe above does not make sense or you require additional info. The existing code is below:

Sub saveRangeToCSV()

    Dim myCSVFileName As String
    Dim myWB As Workbook
    Dim tempWB As Workbook
    Dim rngToSave As Range

    Application.DisplayAlerts = False
    On Error GoTo err

    Set myWB = ThisWorkbook
    myCSVFileName = myWB.Path & "\" & "UploadableCSV" & VBA.Format(VBA.Now, "dd-MMM-yy hh-mm") & ".csv"
    Set rngToSave = Range("B1:CU35")
    rngToSave.Copy

    Set tempWB = Application.Workbooks.Add(1)
    With tempWB
        .Sheets(1).Range("A1").PasteSpecial xlPasteValues
        
        .SaveAs Filename:=myCSVFileName, FileFormat:=xlCSV, CreateBackup:=False
        .Close
    End With
err:
    Application.DisplayAlerts = True
End Sub

 

[DanteAmor]

Try this
I put lr = 2 assuming that your data begins in row 2, but if they start lower, change 2 to the row where your data begins in column B.

Sub saveRangeToCSV()
    Dim myCSVFileName As String
    Dim myWB As Workbook
    Dim tempWB As Workbook
    Dim rngToSave As Range, lr As Long

    Application.DisplayAlerts = False
    On Error GoTo err

    Set myWB = ThisWorkbook
    myCSVFileName = myWB.Path & "\" & "UploadableCSV" & VBA.Format(VBA.Now, "dd-MMM-yy hh-mm") & ".csv"
    
    lr = 2
    Do While Range("B" & lr).Value <> ""
      lr = lr + 1
    Loop
    lr = lr - 1
    Set rngToSave = Range("B1:CU" & lr)
    rngToSave.Copy

    Set tempWB = Application.Workbooks.Add(1)
    With tempWB
        .Sheets(1).Range("A1").PasteSpecial xlPasteValues
        
        .SaveAs Filename:=myCSVFileName, FileFormat:=xlCSV, CreateBackup:=False
        .Close
    End With
err:
    Application.DisplayAlerts = True
End Sub

 

[Anvar]

Try this
I put lr = 2 assuming that your data begins in row 2, but if they start lower, change 2 to the row where your data begins in column B.

Sub saveRangeToCSV()
    Dim myCSVFileName As String
    Dim myWB As Workbook
    Dim tempWB As Workbook
    Dim rngToSave As Range, lr As Long

    Application.DisplayAlerts = False
    On Error GoTo err

    Set myWB = ThisWorkbook
    myCSVFileName = myWB.Path & "\" & "UploadableCSV" & VBA.Format(VBA.Now, "dd-MMM-yy hh-mm") & ".csv"
   
    lr = 2
    Do While Range("B" & lr).Value <> ""
      lr = lr + 1
    Loop
    lr = lr - 1
    Set rngToSave = Range("B1:CU" & lr)
    rngToSave.Copy

    Set tempWB = Application.Workbooks.Add(1)
    With tempWB
        .Sheets(1).Range("A1").PasteSpecial xlPasteValues
       
        .SaveAs Filename:=myCSVFileName, FileFormat:=xlCSV, CreateBackup:=False
        .Close
    End With
err:
    Application.DisplayAlerts = True
End Sub

Hi Dante,

Thank you so much, it works perfectly!

 

[DanteAmor]

Im glad to help you, thanks for the feedback.