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]

OK, I misunderstood. I thought you were wanting to save each separate 6 row segment as its own CSV file (which is why I mentioned I was unsure how you wanted to name each one).
I didn't realize that you wanted one single CSV file, without the 6 row segments.

It looks like you got a working solution from Marc, so I won't bother coming up with new code to do that his already does.

Thank you very much Joe4

 

[Marc L]

Are there any VBA books or anything to help me to learn VBA programming?

All I used within my demonstration is in the VBA help like in the Excel help for worksheet functions CountA & Index …​

I never read any VBA book (already knew BASIC and its childs) but just using the Macro Recorder​

to understand some Excel / VBA basics (statements, Objects Model) via the VBA help​

and of course seeing how some forums helpers have resolved some threads.​

 

[Joe4]

Everybody has different levels of knowledge and preferred learning methods.

If you prefer to learn via book, there are tons of books out there. The MrExcel bookstore sells a bunch: Products

If you prefer to learn using videos or on-line resources, there are lots of tutorials and videos to be found via Google and YouTube searches.

And of course, reading through other threads here can help too!

 

[Jorgi]

All I used within my demonstration is in the VBA help like in the Excel help for worksheet functions CountA & Index …​

I never read any VBA book (already knew BASIC and its childs) but just using the Macro Recorder​

to understand some Excel / VBA basics (statements, Objects Model) via the VBA help​

and of course seeing how some forums helpers have resolved some threads.​

Thank you for the advise Marc L

 

[Jorgi]

Everybody has different levels of knowledge and preferred learning methods.

If you prefer to learn via book, there are tons of books out there. The MrExcel bookstore sells a bunch: Products

If you prefer to learn using videos or on-line resources, there are lots of tutorials and videos to be found via Google and YouTube searches.

And of course, reading through other threads here can help too!

Thank you for the advise Joe4

 

[Jorgi]

Hello, a VBA demonstration to directly export the data from the source worksheet to a csv text file for starters :​

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.Rows
            If .CountA(Rw) > 1 Then Print #F, Join(.Index(Rw.Value2, 1, 0), ",")
        Next
    End With
        Close #F
End Sub

Marc L hope the last question related to this topic. What will be the VBA code if I will add in column A some data which cannot be on the created CSV file. The data will be in the column A just for the user's reference? Thank you so much as always.

Initial file

Outcome from the macro (exactly the same as previously)

 

[Marc L]

​

Exactly the same VBA procedure as this is the same than in the initial post !​

 

[Jorgi]

​

Exactly the same VBA procedure as this is the same than in the initial post !​

I've ran the macro again and the outcome looks as per below. The column A (reference) is included plus the part of the empty rows highlighted in yellow. I probably doing something wrong. Should I change something in the code? Thank you and apologies for being a bit of a pain.
outcome received

the ideal outcome

 

[Marc L]

​

Sorry, I misunderstood as I did not see the column inserted, my bad !​

​

Differents ways … Is it always only from columns B to H, never any additional column like I, J, … ?​

​

​

 

[Jorgi]

​

Sorry, I misunderstood as I did not see the column inserted, my bad !​

​

Differents ways … Is it always only from columns B to H, never any additional column like I, J, … ?​

​

​

It will be always from B to AZ.