Saving worksheet as CSV with correct usedrange...

[Mike Iacovou]

Hi all.

I have sheet(1) which begins filled with data to row 65000... This data is
sorted / filtered, copied to Sheet(2)... at this point, sheet(2) contains 400
rows of data only...
Sheet(1) is cleared (usedrange fix also applied for good measure)...

ie.

sheets(1).usedrange.clearcontents
x = sheets(1).usedrange.rows.count

Sheet(2) is copied back to Sheet(1)... Using the LastRow() function, this
correctly shows that lastrow is 400, as does usedrange. CTRL+END jumps to row
65000... even if I delete rows 401:65000, CTRL+END still jumps to same place.
I use VBA to save the worksheet as a CSV file, and the resultant CSV is
huge, with empty rows to 65000... if I open it and just re-save it, it
correctly saves as 400 rows only...

My question is - is there a means to save this as a CSV with only 400 lines
(without a manual row-by-row parsing) without having to re-save it to reset
the empty row issue ??

TIA

 

[Leith Ross]

Hi all.

I have sheet(1) which begins filled with data to row 65000... This data is
sorted / filtered, copied to Sheet(2)... at this point, sheet(2) contains 400
rows of data only...
Sheet(1) is cleared (usedrange fix also applied for good measure)...

ie.

sheets(1).usedrange.clearcontents
x = sheets(1).usedrange.rows.count

Sheet(2) is copied back to Sheet(1)... Using the LastRow() function, this
correctly shows that lastrow is 400, as does usedrange. CTRL+END jumps to row
65000... even if I delete rows 401:65000, CTRL+END still jumps to same place.
I use VBA to save the worksheet as a CSV file, and the resultant CSV is
huge, with empty rows to 65000... if I open it and just re-save it, it
correctly saves as 400 rows only...

My question is - is there a means to save this as a CSV with only 400 lines
(without a manual row-by-row parsing) without having to re-save it to reset
the empty row issue ??

TIA

Hello Mike,
Have a look at this article. It is simple and works well.

http://articles.techrepublic.com.com/5100-10877_11-6135485.html

Sincerely,
Leith Ross