Insert a blank row when finds one of 4 specific texts in a column of data

  • Hi there


    I am new to macros but have learnt a lot just by browsing the internet as people are so generous with their knowledge.


    My problem is this - I have four columns of data (ie a table) and I need a macro to look down one of the columns (C) and insert an entire blank row everytime it comes across one of 4 specific texts (there are NO blank rows in the data it is looking at).


    For example, column C will look like this before:


    x

    TextA

    x

    x

    TextB

    x

    TextC

    x

    x

    x

    Text D

    x


    And it should look like this after:


    x


    TextA

    x

    x


    TextB

    x


    TextC

    x

    x

    x


    TextD

    x


    If it helps, the data should never go beyond row 400 so getting it to stop at row 1000 is fine rather than doing the whole column.


    I have found macros that will look for one text, but when I copy that for the next text string I think the blank lines already inserted mucks it up.


    TextA and TextB will always appear in the column, TextC and TextD may not, so it has to be able to handle not finding it and carrying on to the end.


    I hope I have described my problem clearly .


    Thank you for reading this post and any help in making me look far clever than I am will be much appreciated. ;)

  • Try:

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • Hello Carim and Mumps


    Thank you so much for replying so quickly.


    Just before I received your replies, I found the following code to insert a row based on the value being 200 (just the example used):


    It then occured to me that perhaps I could put some more if statements in to cover the three other conditions that required a row to be inserted, so I copied the 'If' and 'Then' lines, changed the If to ElseIf and substituted the 200 for my other text strings and it has worked.


    Mumps - your code is much more succinct so I copied that and inserted my text strings and whilst it did insert a blank row, it only did so on the first occurence of each of the four text strings.


    I haven't posted a sample of the data as I am not sure how useful that would be now.


    Thank you both so much for replying so quickly and giving up your time to help macro newbies like myself.


    Please keep up the good work.

  • I assumed you had only one occurrence of each text string. This macro should take care of all occurrences:

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • I haven't posted a sample of the data as I am not sure how useful that would be now ...

    Re,


    With a sample file ... you can illustrate your Before vs After worksheet ... ;)


    Guess what ... it speeds up the whole process of getting your adequate solution ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

  • Well, my elation was short lived ||


    I tested out my code on some new data. It exports as a csv file which i save as excel and then copy the data to my spreadsheet, and after a couple of hours of pulling out my hair and eliminating options, it turns out that the information in the action column (C) has hidden spaces!! Aargh.


    I have no idea why my original data worked as I had not trimmed it, just pasted it as special-values but this does not work for the new data, so the question is ...... can I build that into the code (Mumps, your code doesnt work probably for this reason) or do I have to trim the data first?


    Sorry Carim, I know you are right, I was just trying to avoid having to make up data for confidentiality reasons.


    I appreciate all you help, thanks.

  • Where are the hidden spaces? Are they only at the beginning and end or can they be within the text, for example, Text B?

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • Hi Mumps


    They are probably at the beginning or end as each text is one continous word.


    I realised this morning that I could just write a macro to trim the column which I have done with success.


    I must admit that when I started to look at writing some macros for this spreadsheet I never realised how frustrating and rewarding it was going to be and I have learnt an awful lot from the internet from people giving up their time to help newbies like myself.


    I will certainly give you code a go now the text is trimmed.


    The surprising thing I have found is that recording the macro is sometimes the easy part. For example, if I use Ctrl and arrow down to indicate going to the last cell of data in a column, the macro records it as an absolute cell value which is no good when the next set of data is longer or shorter. Am I missing an obvious way for the macro to record relative/dymanic movement (not sure my english is right there but I think you get the gist).


    Anyway, once again thank you for all your help and time, I really do appreciate it.

  • Recording a macro is a good start but usually it is not as efficient as it could be and it will need revising should anything in the data change. The ranges in the code are also fixed and not dynamic so that also has to be taken into account. If you need any more help, please let me know. :)

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.