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

 

[Marc L]

​

So in red the mod to apply :​

For Each Rw In ActiveSheet.UsedRange.Columns("B:AZ").Rows

 

[Jorgi]

​

So in red the mod to apply :​

For Each Rw In ActiveSheet.UsedRange.Columns("B:AZ").Rows

Marc L - thank you so much again it works perfectly. You are a lifesaver.

 

[Jorgi]

​

So in red the mod to apply :​

For Each Rw In ActiveSheet.UsedRange.Columns("B:AZ").Rows

Marc L, the .Columns ("B:AZ") works perfect but when I was testing it I noticed that if there is a formula in any cell and return "" (empty cell) then the empty line is in the outcome CSV file but it shouldn't be there. Apologies I should mention that there will be a formula and some formulas will be retuning empty cell "" if there will be no data. Is there any solution for this problem? Thank you

initial file

outcome file

 

[Marc L]

​

New fix :​

If (Rw.Cells.Count - CountBlank(Rw)) > 1 Then Print #F, Join(.Index(Rw.Value2, 1, 0), ",")

 

[Jorgi]

​

New fix :​

If (Rw.Cells.Count - CountBlank(Rw)) > 1 Then Print #F, Join(.Index(Rw.Value2, 1, 0), ",")

Thank you Marc L I have run the macro and received an error.

 

[Marc L]

​

My bad as when editing the post for a better way I forgot the dot before the worksheet function CountBlank …​

 

[Jorgi]

​

My bad as when editing the post for a better way I forgot the dot before the worksheet function CountBlank …​

My VBA is so bad I should spot it and not bother you. Marc L thank you so much again.

 

[Jorgi]

My VBA is so bad I should spot it and not bother you. Marc L thank you so much again.

Mark L - how to make the CSV file saved via Macro to include the correct length of number string?

The Example: in the xlsx the number looks like 12345678912345 but when the macro save the file as CSV the number looks like 1.23457E+13. Is there any solution to have the number 12345678912345 in the CSV instead of 1.23457E+13? Thank you

 

[Marc L]

Check first the csv file with Notepad - or any other text file editor - rather than with Excel …​

 

[Jorgi]

​

Check first the csv file with Notepad - or any other text file editor - rather than with Excel …​

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