Save to range up to cells with "" value

Anvar

New Member
Joined
Nov 20, 2019
Messages
9
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?
1574257103862.png

Please let me know if any of tthe above does not make sense or you require additional info. The existing code is below:
VBA Code:
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
 

Some videos you may like

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows
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.

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

New Member
Joined
Nov 20, 2019
Messages
9
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.

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

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows
Im glad to help you, thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,113,904
Messages
5,544,978
Members
410,647
Latest member
LegenDSlayeR
Top