Excel VBA CSV export with to many semicolons

Question

I'm using Excel VBA to calculate dimensions for storage locations in a warehouse. The results are send via CSV to storage machines so that they know how much product can fit in a storage container.

It worked like a charm but now after some finetuning there is a problem.

Firts is the product identifier. In the second column you see 1 to 10. Thats ten different storage containers. Then a minimal amount and then a maximum amount for that storage container.

It looks like this:

000165025W;1;0;;;;;;;;;
000165025W;2;100;1;;;;;;;;
000165025W;3;0;;;;;;;;;
000165025W;4;300;101;;;;;;;;
000165025W;5;450;301;;;;;;;;
000165025W;6;0;;;;;;;;;
000165025W;7;0;;;;;;;;;
000165025W;8;900;451;;;;;;;;
000165025W;9;1725;901;;;;;;;;
000165025W;10;0;;;;;;;;;

Should look like this:

000165025W;1;0;
000165025W;2;100;1;
000165025W;3;0;
000165025W;4;300;101;
000165025W;5;450;301;
000165025W;6;0;
000165025W;7;0;
000165025W;8;900;451;
000165025W;9;1725;901;
000165025W;10;0;

Is there a way to lose all the semicolons or does anyone know whats causing this?

This is the piece of code that is responsible for creating the csv's..

Sheets("NIEUW").Copy
ActiveSheet.Range("1:32").EntireRow.Delete 
ActiveSheet.Range("43:1000").EntireRow.Delete 
ActiveSheet.Range("E:Z").EntireColumn.Delete   

Application.DisplayAlerts = False 
With ActiveWorkbook 

  .SaveAs Filename:=MijnPad & MijnBestandsNaam, FileFormat:=xlCSV, CreateBackup:=False, local:=True
 .SaveAs Filename:=MijnPad2 & MijnBestandsNaam2, FileFormat:=xlCSV, CreateBackup:=False, local:=True      .Close False
 End With

Thanks a lot!

I added a lot of code to existing (working) code.

Answer 1

It seems as though there are blank cells on the sheet that you are copying from. Try changing

activesheet.range("E:Z").entirecolumn.delete

to

activesheet.range("E:XFD").entirecolumn.delete

or, more simply

activesheet.range("E:XFD").delete

This should get rid of any columns after the first 4, the ones with your data