CSV file saves with an extra blank cell

[thereuare]

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.

 

[TryingToLearn]

What about having your code check for Len(activecell)>0

 

[thereuare]

What does Len(ActiveCell)>0 do?

Thanks.

 

[kkknie]

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]

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]

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