Search entire row, copy one below and paste in specific column

[Venteux]

Hi all,

I'm a complete newb with excel vba (vba in general). I usually download some info from a pdf into a csv. The csv is pretty messy, with all of the information displayed horizontally. For example:

---

Course Title_1	Course Hours	Course Prerequisites	Course Title_2	Course Hours	Course Prerequisites	Course Title_3	Course Hours	Course Prerequisites
Math 2	25	Math 1	Language 2	30	Language 1	Science 2	20	Science 1

---

I'd like it to look like this:

Course Title	Course Hours
Math 2	25
Language 2	30
Science 2	20

I copy the sheet and save it into a macro-enabled workbook. I've tried to create a macro based on bits of other code that I have found and came up with this:

Please Login or Register  to view this content.

I used the LIKE operator because I want to find all cells with the word "Title" or "Hours" because each heading may be different (e.g., "Course Title_1", "Course Title_2", etc.). Unfortunately, when I run this macro, nothing happens. Is it because I haven't set a range? I don't know how to select the whole sheet. Some of the csv files may have a lot of courses so it goes all the way to the FZ column for example, while other csv's have 6 courses so it might stop at column R. It varies.

Can anyone help? Like I said, I'm a complete newb so I apologize in advance if the macro above looks completely messy and ridiculous.

[Mumps1]

DO you also want to include the course prerequisites in your result? It would be easier to help and test possible solutions if you could attach a copy of your file. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary). See the yellow banner at the top of this page for instructions to attach a file.

[Venteux]

No, I don't need the course prerequisites. This is what I need:

- I only need the course title and course hours
- I'd like to keep columns A to L as they are
- I'd like to move all of the course titles (Q2, U2, Y2, etc.) and paste them in the next available row in column M
- I'd like to move all of the course hours (R2, V2, Z2, etc.) and paste them in the next available row in column N

A copy of the file is attached. Any help is appreciated.

[Mumps1]

Try:

Please Login or Register  to view this content.

[Venteux]

Wow! Amazing! Thank you so much!!

[Mumps1]

You are very welcome.