Exporting to CSV without formatted blank values

I have a .xlsm with data tabs that's designed to auto populate some cells with data and then export those rows to a CSV file, however when I run my code it copies the entire active range including the cells that have been formatted to be blank and it exports them as commas, I've tried writing the code to delete the commas in the new CSV and I've tried writing the code to not copy over formatted blank values in the first place but nothing so far I've tried has worked.

So far I've spent a few months on this, and other people on the forums have the same issue but no one seems to have fixed it.

Sub Mcam_Order_Entry()
'
' Mcam_Order_Entry Macro
' GETS ORDER ENTRY FORM READY FOR TRUNEST
'
' Keyboard Shortcut: Ctrl+Shift+M
'

Dim SB As Worksheet
Set SB = Worksheets("Sandbox")
Set n = SB.Cells(3, 2)
 

 Worksheets("PNM").Copy
 With ActiveSheet.UsedRange
 .Copy
 .PasteSpecial paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=True, Transpose:=False
 End With
 Application.CutCopyMode = False
 ActiveWorkbook.SaveAs Filename:="I:\Group\DNC\MAGESTIC\Multicam\NEW MULTICAM DXFS\" & "Nest" & n & ".csv", _
 FileFormat:=xlCSVMac
 
 ActiveWorkbook.RefreshAll
 Range("F15").Select
 ActiveWorkbook.Save
 ActiveWindow.Close
 Worksheets("Sandbox").Select
 Range("B3") = Range("B3") + 1
 Worksheets("PNM").Select
 Range("F2:F15").Select
 Selection.ClearContents
 Range("F2").Select

End Sub

Solution

Try,

Sub setRangToCsv()

    Dim Filename As String
    Dim rngDB As Range
    Dim Ws As Worksheet
    
    Set Ws = ActiveSheet
    Set rngDB = Ws.UsedRange

    Filename = "comacsvtest.csv"
        
    TransToCSV Filename, rngDB

End Sub
Sub TransToCSV(myfile As String, rng As Range)

    Dim vDB, vR() As String, vTxt()
    Dim i As Long, n As Long, j As Integer, k As Integer
    Dim objStream
    Dim strFile As String

    Set objStream = CreateObject("ADODB.Stream")
    vDB = rng
    For i = 1 To UBound(vDB, 1)
        n = n + 1
        k = 0
        Erase vR ' add  this  line
        For j = 1 To UBound(vDB, 2)
            If vDB(i, j) <> "" Then
                k = k + 1
                ReDim Preserve vR(1 To k)
                vR(k) = vDB(i, j)
            End If
        Next j
        ReDim Preserve vTxt(1 To n)
        vTxt(n) = Join(vR, ",")
    Next i
    strtxt = Join(vTxt, vbCrLf)
    With objStream
        '.Charset = "utf-8"
        .Open
        .WriteText strtxt
        .SaveToFile myfile, 2
        .Close
    End With
    Set objStream = Nothing

End Sub