Remove Blank Cells and Sequential Series to Compact List of Elements

[JCatlow]

Hello,

I am new to this forum looking for some assistance to help me write code to remove blank spaces and condense sequential elements into a short hand the schema of which can be seen in the snippet below and in the workbook sheet attached. Essentially, using this shorthand schema, when more than two elements are sequential in the list the intermediate values are omitted and the first and last in the series is separated by “ TO ” where individual elements and compacted sub-lists of sequential elements are separated by spaces.

Example Element by Element List

68052 68055 68064 68065 68066 68079 68080 68081 68082 68083 68111 68112 68113 68126 –
68127 68128 68129 68130 68158 68159 68160

Resulting Compacted List
68052 68055 68064 TO 68066 68079 TO 68083 68111 TO 68113 68126 TO 68130 –
68157 68159 68160

Ultimately, what is required is to process each column of data captured from a separate analysis and write the compacted list back to the sheet to be used in the next analysis iteration. The number of columns is variable as are the number of rows from one analysis to the next so a dynamic array would seem to be the appropriate choice here but not absolutely required. Lastly, each row from the compacted list may be not more than 77 characters wherein it must continue to next line with a space and a dash “ –“ which cannot occur before or after the “TO” ( e.g. the sub-lists cannot be split at end of line).

Sadly, I have exhausted my limited resources for array processing, nested arrays and DO loops and am at an impasse. Moreover, I am unable to web mine any VBA code snippets similar enough to scavenge for this purpose. Any assistance (even a helpful nudge in the right direction) would be greatly appreciated and thank you in advance for any assistance you may provide. Please note that although I have stated the problem in its entirety, it is not expected that someone will invest all the time necessary to provide a comprehensive code solution to the problem in its entirety and partial solutions to any and all parts are welcomed and appreciated.

[jindon]

See if this is what you wanted.

Please Login or Register  to view this content.

[JCatlow]

Jindon,

Thank you very much for response, the code works flawlessly.

Your reputation is well deserved and I am humbled by your abilities.

[jindon]

You are welcome and thanks for the rep.