CSV file saves with an extra blank cell

thereuare

Board Regular
Joined
May 28, 2002
Messages
232
I have a program that downloads a CSV file from the web, sorts the data, and then saves it. Later in the program this file is called and the data is read using a "Do until end of file" command.

All works well for most users, but two have reported that the program causes them to get caught in a loop.

I have discovered the problem to be a cell after the last row of data (ie- if the data ends in row100, then cell(A101) appears to be empty but it's not... maybe there's a blank space in it?). Therefore, since the program 'thinks' that A101 isn't empty, it attempts to read it, but since there's no value the program gets caught in a loop. If i delete the cell after the last row of data (A101 in this instance) the program will then be able to read this file fine.

I know it would be easy to just have the program delete this cell, but the number of rows is not constant and i don't want to just delete the last row since that would eliminate the last row of data for users that are not encountering this problem.

Any suggestions for a 'fix' or is it possible that there is a setting on the computers of these two users that is causing the last cell to save with something non-visible in it?

Thank you.
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

kkknie

Well-known Member
Joined
Apr 29, 2002
Messages
677
Len(ActiveCell)>0 gives the number of characters in the Active cell...

I think TryingToLearn is suggesting that you delete the last row only if it is blank. Len(ActiveCell) would not give a 0 if there was a space in the cell.

I would suggest the following structure:

1. Go to the last cell
2. If it is blank [i.e. If Trim(YourCell.Value)="" or If Len(Trim(YourCell.Value))=0] then delete it.

Note: The "" are two consecutive double-quotes which indicate a blank field.

K
 

thereuare

Board Regular
Joined
May 28, 2002
Messages
232
Thanks for the explanation on Len

I think we came up with similar solutions (i was trying some things as you replied). Since ColumnA should always be a number, i used the following:

Range("A1").End(xlDown).Select
If Not IsNumeric(ActiveCell) Then Selection.Delete Shift:=xlUp

I assume this is no faster/slower than the code you presented? Although they're almost identical, please let me know if you see any problem with my solution.

PS- i'm still perplexed/curious as to why this happens on some machines and not others.
 

kkknie

Well-known Member
Joined
Apr 29, 2002
Messages
677
Your method is just fine (possibly even better than mine).

As for why these things happen differently on different machines, I can only
say that I have seen numerous posts about "mysterious" things appering
at the bottom of people's worksheets. I've also never seen anything better
than a brute force delete to remove them.

Therefore, when saving as a CSV, Excel uses the entire range (or what it
erroneously thinks is the entire range) to save, thus adding your spaces.

It reeks of a Redmond conspiricy...

K
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,322
Messages
5,769,451
Members
425,548
Latest member
macjagger17

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top