I have been searching for a routine to solve a problem, I know somebody has to
have solved this before, no luck.

I am trying to import a csv file. The file is 10k+ lines long. Every so often
in those lines, there is an extra comma in the original csv file. This, of
course, causes that line to end up with a blank cell, the rest of the cells to
the right are out of alignment with all the other cells. This can happen
anywhere from once or twice to hundreds of times per csv. The csv is supplied
to me by another company, having them fix the original error is out of the
question. Ultimately, a non-technical person is going to end up with this
solution, so while I could just fix the problem myself by manually cutting and
pasting, I don't want to rely on the user doing this every week, hence the need
for a vba macro.

The blank cell occurs in column K. I can get all the data sorted so that the
blanks in column K are sorted to the top, with the blank starting at K1.

So the logic would go:

Start in cell K1.
If cell K1 is not blank, stop the macro.
If K1 is blank, cut range L1:AI1, paste into range K1:AH1, ignoring the message
about overwriting existing data. Move to cell K2 and repeat, if cell K2 is not
blank, stop the macro, etc.

Once I get the data back in line, I think I can handle what I need from that
point forward.

I am using Excel 2003.

Many thanks in advance for any assistance you are able to provide!!!

If there are not supposed to be any empty cells in the data after you've
imported the CSV file, you may want to try this.

Edit|goto|special|blank cells
Edit|delete
Shift cells right

If you can have empty cells in the data, you could select column K and then use
the same technique.

Melvin Purvis wrote:

I have been searching for a routine to solve a problem, I know somebody has to
have solved this before, no luck.

I am trying to import a csv file. The file is 10k+ lines long. Every so often
in those lines, there is an extra comma in the original csv file. This, of
course, causes that line to end up with a blank cell, the rest of the cells to
the right are out of alignment with all the other cells. This can happen
anywhere from once or twice to hundreds of times per csv. The csv is supplied
to me by another company, having them fix the original error is out of the
question. Ultimately, a non-technical person is going to end up with this
solution, so while I could just fix the problem myself by manually cutting and
pasting, I don't want to rely on the user doing this every week, hence the need
for a vba macro.

The blank cell occurs in column K. I can get all the data sorted so that the
blanks in column K are sorted to the top, with the blank starting at K1.

So the logic would go:

Start in cell K1.
If cell K1 is not blank, stop the macro.
If K1 is blank, cut range L1:AI1, paste into range K1:AH1, ignoring the message
about overwriting existing data. Move to cell K2 and repeat, if cell K2 is not
blank, stop the macro, etc.

Once I get the data back in line, I think I can handle what I need from that
point forward.

I am using Excel 2003.

Many thanks in advance for any assistance you are able to provide!!!

--

Dave Peterson

Dave Peterson wrote:
If there are not supposed to be any empty cells in the data after you've
imported the CSV file, you may want to try this.

Edit|goto|special|blank cells
Edit|delete
Shift cells right

If you can have empty cells in the data, you could select column K and then use
the same technique.


Many thanks for your quick reply!

DUH, no wonder I couldn't find any help on my own, I was trying to make the
solution far too difficult.

i am getting the project whittled down to size, but I have 2 more questions
which I will post under separate subjects...