Compare column number of a cell with the last cell column number in a loop

I am trying to save an excel sheet to .csv format with the following macro:

Please Login or Register  to view this content.

The following part of the code needs to be modified so that the commas appear correctly in .csv file even for null values for any given column.

Please Login or Register  to view this content.

I have attached the Sample Sheets. Source sheet and the Result sheet. The Result sheet doesn't provide the required result. Some data are missing and unwanted commas have been added. Please help me to correct the logic in the above piece of code. Thanks!

Excel can directly save a workbook to a .csv file with Save as command …

Originally Posted by Marc L

Excel can directly save a workbook to a .csv file with Save as command …

Hi Marc,

Thanks for the response! I know that we can save Excel workbook as .csv file. But i need to save .csv with UTF8 encoding which is why i am using the above macro.

I had taken the reference from "http://techspread.wordpress.com/2012/12/05/export-excel-to-csv-in-unicode-utf-8-using-macro-vba/

And what about the CSV (DOS) file format ?!

Originally Posted by Marc L

And what about the CSV (DOS) file format ?!

I tried its giving undesired results! For example ú is getting converted to £ !

Maybe it would be easier for you to let Excel save a normal CSV file
and after you just have to convert it to UTF8 …

Originally Posted by Marc L

Maybe it would be easier for you to let Excel save a normal CSV file
and after you just have to convert it to UTF8 …

Can we not do anything with the existing macro?

Existing macro is crappy !

Please Login or Register  to view this content.

Originally Posted by Marc L

Existing macro is crappy !

Please Login or Register  to view this content.

That worked perfect! :D Though i am still trying to figure out what exactly you did here :P

I just placed a comma between each column, line by line …

See the help of worksheet function INDEX and the one of VBA function Join …

Sure I will! Thanks a lot Marc!

Originally Posted by Pavan Renjal

Sure I will! Thanks a lot Marc!

Hi Marc,

I am not sure if i need to open a new thread since this thread has been marked as solved. But i am running into few issues which i am not able to fix.

I receive Run-Time error '13' Type mismath error. We use large number of data(more than 65k records) and it appears to me that this might be causing the issue here. Can you please suggest any workaround for this? It is also taking a lot of time to save when the numbers are huge.

The error appears on the following code:

Please Login or Register  to view this content.

Can anyone help me on this please?

Hi,

what's the Excel's version ? What is the R value at the error time ?

Originally Posted by Marc L

Hi,

what's the Excel's version ? What is the R value at the error time ?

Hi Marc! Thanks for the response! The R shows value of '1' . My Excel version is MS Excel 2010, Version: 14.0.6129.5000 (32-bit)

Means that issue is yet on the first line of data like if there is only one data line, I don't understand …

Index needs at least 2 data lines … Row #2 is not empty ?
Because CurrentRegion is a range bounded by any combination of blank rows and blank columns.

Error #13 means data type is not the expected one …

Originally Posted by Marc L

Means that issue is yet on the first line of data like if there is only one data line, I don't understand …

Index needs at least 2 data lines … Row #2 is not empty ?
Because CurrentRegion is a range bounded by any combination of blank rows and blank columns.

Error #13 means data type is not the expected one …

Row #2 is not empty! I have 1.5 Lakh rows in my sheet!

To test this i just reduced the number of records to just 10. I was able to save it correctly. It appears to me that it has something to do with the size of the data.

Test with 65 536 rows. If success, test with one more row, aim is to find the row limit …

Originally Posted by Marc L

Test with 65 536 rows. If success, test with one more row, aim is to find the row limit …

I have tried saving the data but it seems like the application hangs for even data that is equal or less than 65536 rows but it doesnt error out which means the code might be still executing.

As I wrote, aim is to find the row limit …

I have also noticed that the execution time increases as the number of rows increase. And for data as huge as this (65536 rows) the application itself hangs

Let me check for today, i will leave the application as is though it appears as 'not responding' at the moment. If the application is able to execute and save the records i will notify you.

Other way is to directly loop row by row without using Index with an array variable
but time process should increase …

Please Login or Register  to view this content.

Originally Posted by Marc L

Please Login or Register  to view this content.

Absolutely flawless execution! Thanks a ton dude! This has literally saved my ***. Total support \m/

Originally Posted by Marc L

You're welcome !

You aim right : some inner Excel's functions are limited in their arrays …
I read it somewhere but I did'nt find any notice.

As Index function needs at least two rows, I give it just a pair …

To be frank, i am still trying to figure out how you did this :D Need to dig a bit more on INDEX, JOIN.

Originally Posted by Marc L

Join & Split are like Cats & Dogs, Sun & Moon or Yin & Yang :
one creates a string with a separator from an array,
the other creates an array from a string cut by a separator.

Join([{"Felix","The","Cat"}], "-") = "Felix-The-Cat"

Split("Danny-The-Dog", "-") = [{"Danny","The","Dog"}]


The worksheet function INDEX extracts - in this case - a row from a group of rows …

Thanks for the explanation Mark! I believe this should help me get started