Newbie want to take out empty cells with macro

Hello World,
I`m 17 years old and got the pleasure to play with macros in my company.
I`m a newbie..:D

Soo, my Problem:
I have a .csv with about 2000 records witch describes persons.
Between the name and the data of these persons is one column for the title and 0-9 empty Cells.
Some persons have no title, so the Cells of the second column are sometimes empty.
A Person can have 0-200 of these "data-cells".

[Robert][Executor][Empty Cell][Empty Cell][Empty Cell][Empty Cell][Data1][Data2][Data3][Data4][Data5][Data6][Data7]
[Henry][Chief][Data1][Data2][Data3][Data4]...
[Angie][Empty Cell][Empty Cell][Empty Cell][Empty Cell][Data1]
[Roland][Empty Cell][Empty Cell][Empty Cell][Empty Cell][Empty Cell][Empty Cell]...
[Sue][Worker][Empty Cell][Empty Cell][Data1][Data2][Data3][Data4]...

I want to take out the empty cells, except the column "Title".
In the end, it should look like this:

[Robert][Executor][Data1][Data2][Data3][Data4][Data5][Data6][Data7]
[Henry][Chief][Data1][Data2][Data3][Data4]...
[Angie][Empty Cell][Data1]
[Roland][Empty Cell]
[Sue][Worker][Data1][Data2][Data3][Data4]...

In my opinion, i should build a for loop that removes the data from the next cell and put it into the actual cell, but only if the actual cell was empty..
The problem would be the column "Title", witch cells have to remain empty, if they were empty.
I could solve the problem with extracting the column "Title" to another excel, delete the data from column B, export csv, import csv comma delimited, search_replace the ",," with "," ten times and insert the column "Title" but this solution isn`t elegant, not efficient and not comfortable for my co-workers.

Anybody knows a solution?

PS. Sorry for my bad English, hope you could understand..

Untested (no computer today) but maybe something like:

Please Login or Register  to view this content.

Adjust columns (C and HZ) and rows (2000) to meet your requirements and check syntax.

Test on a copy of the workbook/worksheet.


Regards, TMS

You're welcome. Thanks for the rep.

To be fair, not so long ago, I would probably have coded a double look processing some 40,000 cells one by one. . We all live and learn


If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.