VBA code to Export selection as .csv file (with specified seperator).

[NessPJ]

Hey Guys,

For a file i made i am looking for a piece of code which allows me to export a selection of cells to a .csv file directly.

I have a selection of cells, for example:
A10:AA5000 filled with VLOOKUP formulas.
I would like the selection to be made only for the cells that contain output (cells that do not contain output are shown as blank by the formula).
So for example only selection A10:AA3499 are filled with values (the rest below them do not show results from the formula).

I should also be able to specifiy the seperator used.

Right now i have a macro that copies the selection to a blank file and then saves it as a .csv this results in a csv file with the wrong seperator.
I would very much like to solve this by using some code rather then being dependant on the language settings of Windows/Office.

Thanks in advance for any help.

 

[Nykyta]

Who told you that? That will (and was) never be the case. You need to reread your excel books.

CSV files (and tab and any other separator used) are recognize empty values so it will be:

col1, col2, col3
1,a,b
2,,c
3,d

I am a bit confused to why you would only want the code to run on cells that contain output
because this would mean the result could be out of sync with other data in terms of columns.

For example, suppose your data looks like this:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]col1[/TD]
[TD]col2[/TD]
[TD]col3[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]a[/TD]
[TD]b[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD][/TD]
[TD]c[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]d[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

when you export this as CSV as you've mentioned,it would look like

col1, col2, col3
1, a, b
2, c
3, d

Then when you've imported the CSV file again for re-evaluation, it would look something like:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]col1[/TD]
[TD]col2[/TD]
[TD]col3[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]a[/TD]
[TD]b[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]c[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]d[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

which is totally out its original column!
Is this okay?