VBA to save rows as CSV without empty rows

[Jorgi]

Hello may I ask you to help me with VBA. I would like to save data via macro as CSV but only if there is a data. Every 6 rows equal to one piece of data element but sometimes the 6 rows can be empty (apart from column A) because there is no data and the file will have to be saved without the 6 empty rows. Thank you very much for your help

Initial file

outcome from macro

 

[Jorgi]

Yes, notepad shows correct length of the number. Thank you again

Marc L is there any other way to save the CSV file in the same place where is the main file? I have tested it on a share point and getting an error while the file is trying to save but when I try to generate CSV and the file is on my desktop drive then there is no issues and the file is saved perfectly. I'm not sure what needs to be added to the macro. Thank you very much as always for your help.

 

[Marc L]

​

To see with your IT …​

 

[Jorgi]

​

To see with your IT …​

Marc L other macros work fine on desktop drive and share point please see below:

 

[Marc L]

​

Remove the On Error codeline and try …​

 

[Jorgi]

​

Remove the On Error codeline and try …​

Apologies for the confusion but I meant the previous code works with share points but this one below doesn't work if the main file is located on share point. It perfectly works if the file is located on my desktop drive. Thank you

Sub Demo1()
Dim F%, Rw As Range
F = FreeFile
Open ThisWorkbook.Path & "\Export.csv" For Output As #F
With Application
For Each Rw In ActiveSheet.UsedRange.Columns("B:AZ").Rows
If (Rw.Cells.Count - .CountBlank(Rw)) > 1 Then Print #F, Join(.Index(Rw.Value2, 1, 0), ",")
Next
End With
Close #F
End Sub

 

[Marc L]

​

Read post #34 then apply & try your 'working' code …​

 

[Jorgi]

​

Read post #34 then apply & try your 'working' code …​

Marc L the code you are referring to is something entirely different. I would like your code (below) works on the share point. Please see the code below this one is not working on SP. It only works on desktop drive. In this code below there is no on error codeline. I'm not sure how to modify your code below I'm so sorry and thank you if you can help.

Sub Demo1()
Dim F%, Rw As Range
F = FreeFile
Open ThisWorkbook.Path & "\Export.csv" For Output As #F
With Application
For Each Rw In ActiveSheet.UsedRange.Columns("B:AZ").Rows
If (Rw.Cells.Count - .CountBlank(Rw)) > 1 Then Print #F, Join(.Index(Rw.Value2, 1, 0), ",")
Next
End With
Close #F
End Sub

 

[Marc L]

​

As you can not write you have a 'working' code just posting an not complete code and far above all starting with On Error !​

The reason why you must try first your 'working' code after removing this codeline …​

Then if it still works without so you must see with your IT why my procedure does not work under SP.​

​

As you can create the file on a local drive then copy it to SP …​

 

[Jorgi]

Hi Marc L, How the VBA code will look like if we want to change it from CSV format to xlsx? Thank you

 

[Joe4]

Hi Marc L, How the VBA code will look like if we want to change it from CSV format to xlsx? Thank you

As his icon shows, Marc's account was banned so he is no longer active on this forrum.
You may be better off posting a new thread, so it appears as a new unanswered question.