Automating tedious functions

[Fishboy]

Hi all,

I am looking to automate some of the more time consuming and tedious steps in my reporting process in Excel 2010.

At present I copy / paste data from a website into sheet 1 (labelled "Source"). I then use a CONCATENATE formula on sheet 2 (labelled "Template") to read from column C of "Source" to combine a variable length list of items into a single word-wrapped cell on "Template". To add confusion, I need to include a prefix of "- " before each entry, and each entry has a carriage return to keep the entries on separate lines within a single cell.

I am currently using the following formula which I shorten or lengthen as required each time:

=CONCATENATE("- ",Source!$C$2,CHAR(10),"- ",Source!$C$3,CHAR(10),"- ",Source!$C$4,CHAR(10),"- ",Source!$C$5,CHAR(10),"- ",Source!$C$6,CHAR(10),"- ",Source!$C$7,CHAR(10),"- ",Source!$C$8,CHAR(10),"- ",Source!$C$9,CHAR(10),"- ",Source!$C$10)

I am wondering if there is a way I can create a macro to do this for me on the press of a button but dynamically change the length of the list based on cell selection, i.e. if there are only 4 entries to be concatenated, I select all 4, press the macro button and it runs through the above formula as far as the 4th entry.

Likewise if I have 12 entries, select the 12 desired cells, press the macro button and hey presto, the macro runs a far as the 12th entry.

I suspect that if it is possible I will need to have a formula with a maximum length of say 20 entries, but the macro understands the desired number entry to stop on based on the cells I have selected.

Any advice would be greatly appreciated.

 

[Fishboy]

Hmmm, alternatively, as the data to be concatenated is always in column C despite having a variable row count, could a macro work on a cell range (say C2:C22) then ignore the blank rows?

Therefore if there were 5 entries in my list, it would concatenate them, prefixing each entry with "- ", adding a carriage return on the end of each entry, but as C7:C22 were empty it would not try to give me 15 extra lines of just "- "?

 

[Fishboy]

Sorry to bump my own thread, but has anyone got any suggestions on how this may be achieved?

Thanks in advance

Fishboy