Posts by Justin Doward

    Hi Mahesh,

    I have removed the .select from your code, I have not tested it so let me know if it works.

    Also note that your method for declaring variables needs to be in full:

    Dim rng, Invalidrng as Range results in rng being declared a variant not a range, it should be Dim rng as range, InvalidRng as range

    As Jolivanes above is indicating, do not forget to wrap your code.


    Good morning,

    The error was due to me getting a bit sloppy, I had code outside the If statement meaning it would run on any change event I have fixed that below.

    I have added the functionality you requested for all words to appear in the B1 merged area.

    I have added some comments to identify the working of the code, let me know how it goes.

    Hi Pete,

    Sorry I missed the message regarding your eye and surgery, my aunt just had that done and it has worked great! hope you get 20/20! I am in Melbourne Australia.

    I will have a look at the question you included there tomorrow, feel free to send me a message to clarify and good luck with the surgery.

    Use the code here, I just noticed the code you are using is the original I posted which I changed. It is apparently best to avoid exiting a loop as I did originally.

    I added functionality to ensure it is a unique list:

    The "option compare text" line needs to go at the top of the page and is separate to the sub. This line means it will ignore the case eg Big, big and BiG will be seen as the same, if you do not want this remove the line.

    I think you want a worksheet change event placed in the code of for the sheet, try this one:

    This transfers the word to the list in column C of sheet4 (whose code name is sheet3) after you hit enter on the cell AD6 in sheet3 (or whichever sheet you add the code to).

    To get rid of the CO columns just add the second line as I had it in the first example, immediately beneath line 31 in your example:

    1. If c.Column > Application.Match("TOT 1", HDRnge, False) And c.Column < Application.Match("PEN.2", HDRnge, False) Then GoTo MyNxt

    The order of the output is determined by the input, otherwise if you know how you want them and you dont want to modify the original you could code them to move... is the order of the output in your last post what you want?

    HI Lukas,

    This version runs faster, only copies the values and adapts to the changed line.

    Hi Lukas,

    This does the job for the example file you sent through (assuming you move the header row to A and insert another sheet.)

    I have made the modifications to the code as suggested in my previous post - I had left out the FALSE option in the application.match function previously so it was not working as it should but it is now. The question is still what is the difference between the CO1-CO5 which you do want to copy and the CO6-CO10 which you do not want to copy? are CO6 and CO10 always bordered by TOT1 and PEN.2 for example?