+ Reply to Thread
Results 1 to 5 of 5

Add rows and search/copy/paste text into added rows

  1. #1
    Registered User
    Join Date
    09-18-2008
    Location
    Devon
    MS-Off Ver
    2002
    Posts
    71

    Add rows and search/copy/paste text into added rows

    Hi all

    Hope someone can help here. My knowledge of VBA is pretty basic, so please bear with me. I will try to explain what I need as best I can!

    I want to create some code that will locate instances of text in 1 column within my data where the text changes, and I would like a row added above that instance. There are a set number of text strings that will be used within this column, and the data will already be sorted so that groups of the same text string sit together. I need to add a row above each grouping.

    Within this row I need to add the text string found within the column in cell 1. When all of the rows have been added and the text added, the column the search was completed in can be deleted.

    For example, I have data in columns A to P with a title row. The column I need to use to search with is column E. Within this column the following instances may be used:

    Adults
    Mental Health
    In-House
    Strategic
    External Provider

    So I need Excel to add a blank row above the "Adults" section. After a number of rows (different each time) the text in column E will change to something else. Above the new text sting I would need a new row adding and the new text adding in Cell 1 for this row.

    I won't know how much data I will have each time I use the code - it will change each time. Also, it could be the case that one of the instances (e.g. Mental Health) will not appear at all - can this be accounted for?

    I hope this makes sense and someone can help! I've added a basic example of what I am looking for.
    Attached Files Attached Files
    Last edited by HelenW; 10-14-2010 at 04:06 AM.

  2. #2
    Forum Guru
    Join Date
    08-26-2007
    Location
    London
    Posts
    4,606

    Re: Add rows and search/copy/paste text into added rows

    If I understand correctly, and your data are already sorted, does this help?
    Please Login or Register  to view this content.
    Last edited by StephenR; 10-12-2010 at 12:22 PM.

  3. #3
    Registered User
    Join Date
    09-18-2008
    Location
    Devon
    MS-Off Ver
    2002
    Posts
    71

    Re: Add rows and search/copy/paste text into added rows

    Thanks Stephen - that works perfectly! Could I ask whether you could add in one small amendment so that each of the added rows is aligned left and bold please? I'm not sure how to reference the added in row. Oh, and I've just noticed the top row that is added copies the formatting from the header row. Is there any way to stop that?

    Thanks so much for your help!

  4. #4
    Forum Guru
    Join Date
    08-26-2007
    Location
    London
    Posts
    4,606

    Re: Add rows and search/copy/paste text into added rows

    Try this. If you want the whole row bold and left-justified add .EntireRow in front of the two respective lines.
    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    09-18-2008
    Location
    Devon
    MS-Off Ver
    2002
    Posts
    71

    Re: Add rows and search/copy/paste text into added rows

    Sorry - forgot to mark this as solved! Thanks for your code anyway :-)

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1