clean up data

I have an excel sheet that i export from a program and need to clean it up.

I am having trouble and working for days. I need the data to look like what is in the second tab.The data in the first tab is the exported data. I can not find out how to merge and get everything on same row and only one row. This needs to be done via a macro. I have a lot of data but this is just some of it.

if i unmerge the worksheet it then puts the data on two separate rows . Then i do not know how to join the rows.

I would love to see the code so i can learn from it. This is driving me nuts .

thank you so much. i have to read your code to see how it is doing everything. It works on my small dataset. But i have to clean up more in the sheet at this time.

you are correct sir. i should have put the full code. i just figured it would not have been that easy. Here is the full code. I been trying to delete the rows 1,2,3 and 4. Then delete any cell row that contains "store " and "000000000" and any blank lines. That should then take out rows 1,2,3,4,6,7,24-28,45-47

the other data will repeat the same way. That is why a search for those words would be great. I tried with if then and could not get it and thought maybe a switch command. But it does not work and i am tying because of the cells being merged. I have more stores but they show up in the report the same way.

i understand if you do not want to take a peek and i can start another post in the other section. Just thought i would show you want the report looks like

thank you so much. I am trying to learn I do not understand this line or how you went and deleted stores and 0000000 . can you explain further ?

.Columns(7).Resize(, 3).EntireColumn.Delete

i tried to google the answer but too many results.


Do you recommend any book , class , video series , or anything to learn how to do stuff like that. i never would have thought of doing some of the commands. I am a c sharp guy and trying to transition into excel more.