Vba code to find columns with any data move over to copy data then insert row below and paste both items

  • Ok, with a lot of help I have been able to create several excel vba modules and they have been unbelievable helpful, but I do not know where to begin with this one.


    I have Column D that has a master number. In column E is misc info, Column F is the Main field that I need (product in this case) . In Column G thru AK are acceptable product names that match the master number. So what I need is to figure if there is a way to write a code that says:


    For every number in column D, Check column G through AK for text. For text found in each column, I'll need to copy that master number, insert a row, put that master number in column D of new row and move the text from the original column G through AK to Column F in new row.


    So my excel sheet looks like this:


    Column D Column E Column F Column G Column H Column I Column J Etc

    12345 XXXXXXX Soap Soap Bar Soap Crystal Soap Liquid Soap Foam

    12222 XXXXXXX Soda Soda Can Soda Bottle blank blank


    And I need it to become this:


    Column D Column E Column F Column G Column H Column I Column J Etc

    12345 XXXXXXX Soap

    12345 XXXXXXX Soap Bar

    12345 XXXXXXX Soap Crystal

    12345 XXXXXXX Soap Liquid

    12345 XXXXXXX Soap Foam

    12222 XXXXXXX Soda

    12222 XXXXXXX Soda Can

    12222 XXXXXXX Soda Bottle



    Is there a way to do this in code? I know how to search for specific data and copy it and move it with Select Case but asking it to look thru 31 columns ignore if they re blank and then copy the text in that column and another specific column is way out of my league. I have zero experience with loops.


    Any and all code assistance would be greatly appreciated.


    Kathy

  • Possibly...

    If I've been helpful, let me know. If I haven't, let me know that too.

  • Is there anyway you could comment this out for me. It's doing something, just not the correct thing. I should have mentioned that I do also have data in columns A though C. I see that your code says assumes data begins A1. The data that needs rearranging begins in D1. What would I change for this?


    Thank you so much for what you have provided so far.

  • A small change and added comments...

    It would be easier if you could provide a workbook with 'dummy' data to test the code.

    If I've been helpful, let me know. If I haven't, let me know that too.