Export to csv using Named Range

[bmorse]

Hello!
I am trying to export 5 columns of data. The rows change each time the data is refreshed. I have a named range of "EquipCosts" for the data. Using the below code, exports, but it give me quotes and commas for the blank rows. Is it possible to export to a csv using a named range? If not, how can I just export the rows that are filled starting with A7? Thanks much for your help!

Sub exportRangeToCSVFile()


Dim myCSVFileName As String
Dim myWB As Workbook
Dim rngToSave As Range
Dim fNum As Integer
Dim csvVal As String


Set myWB = ThisWorkbook
myCSVFileName = "K:\Equipment" & "" & "CSV-Exported-File-" & VBA.Format(VBA.Now, "dd-MMM-yyyy hh-mm") & ".csv"
csvVal = ""
fNum = FreeFile
Set rngToSave = Range("A7:E500")


Open myCSVFileName For Output As #fNum


For i = 1 To rngToSave.Rows.Count
For j = 1 To rngToSave.Columns.Count
csvVal = csvVal & Chr(34) & rngToSave(i, j).Value & Chr(34) & ","
Next
Print #fNum , Left(csvVal, Len(csvVal) - 2)
csvVal = ""
Next


Close #fileNumber
End Sub

 

[Joe4]

Welcome to the Board!

I think the easiest thing to do is to copy just the range you want to save to a CSV to a new workbook (or new sheet within your active workbook), then just do a SaveAs CSV.
That avoids all loops and issues that you are talking about.

Will that suffice?

 

[bmorse]

Sure, I can try that. I will look up that code...

 

[Joe4]

Sure, I can try that. I will look up that code...

Shouldn't need to look up too much. You can get a lot of it with the Macro Recorder (especially the SaveAs part).

And if the data you want to copy is all contiguous, you may be able to use Current Region to dynamically select and copy it.
Or, we can help you dynamically program how to find the end of the data, i.e. if there is always data in column A, you could do this:

Dim lr as Long
lr = Cells(Rows.Count,"A").End(xlUp).Row
Range("A7:E" & lr).Copy