Need help new to VBA, working with exporting to csv of course row 1 has more cells then other rows which causes trailing commas for rest of rows

[SWAY14]

Hello, Im new to VBA and I am trying to export sheet 1 to csv but since im importing to a system they do not like trailing commas.
I have my code which does a great job of selecting rows with information but since I am using row 1 as headers and it goes all the way to CQ
but I know that all the other records starting at row 2 will end at CN which leaves me with all rows from row 2 forward with 3 trailing commas at the end. The thing is I really only need 1 of the trailing commas for the system to validate my file. Any help will be amazing!


My code
Sub Thistheone()
'
' Macro3 Macro
'

'
Application.DisplayAlerts = False
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Workbooks.Add
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveWorkbook.SaveAs FileName:= _
"/Users/sway/Desktop/cleint uploads /Testing2.csv", FileFormat:=xlCSVUTF8, _
CreateBackup:=False
Application.DisplayAlerts = False
ActiveWorkbook.Close True



End Sub

what I am trying to fix when exported
from this
1065,,,,,2,,,
to this
1065,,,,,2,

 

[MoshiM]

Copy the wanted range to a new worksheet via vba , export and then delete the generated worksheet

Or

1. Read the contents of the csv into a string variable
2. Split the string into an array with vbcrlf/vbnewline as the delimiter
3. Iterate over the each character in the first element of the array starting from the right
4. replace with an empty string until you find something that isn't a comma
5. Join array back together and overwrite the csv file

 

[SWAY14]

Copy the wanted range to a new worksheet via vba , export and then delete the generated worksheet

Or

1. Read the contents of the csv into a string variable
2. Split the string into an array with vbcrlf/vbnewline as the delimiter
3. Iterate over the each character in the first element of the array starting from the right
4. replace with an empty string until you find something that isn't a comma
5. Join array back together and overwrite the csv file

Thank you for your input thing is the range will always change can be 2 rows or 50 rows and when I do like 50 to be on the safe side I get 50 rows or just commas trailing at the end

 

[MoshiM]

Range object (Excel)

Office VBA reference topic

docs.microsoft.com

range("A1:CN" & range("CN" & activesheet.rows.count).End(xlUP).row).copy