Get data from split/merged cells

[garymrowe]

Hi All,

Before joining and posting my question I’ve used the search function and not found and answer to my problem. Have found plenty of messages from people to others telling them to steer well clear of merging cells because it just causes grief but unfortunately this workbook has been given to me and I’m trying to convert the information so I can import it into an SQL database.

I’ve attached a small version of the workbook because my explanation confuses myself let alone others.

http://www.garyrowe.co.uk/stuff/xls/test.xls

On the SOURCE sheet is the table I’m struggling to work from. It has information on Bridges that I’m trying to merge into one cell with HTML code wrapped around it. This is simple as I’ve created the code on the OUTPUT sheet.

However due to the fact that there are split/merged cells describing which Lines and Protecting Signals surround a bridge when I auto fill down on the OUTPUT sheet I get a lot of data missing from the initial row and then rest appearing in the cells below but with some information missing.

My ideal OUTPUT would have all the information for one bridge per row wrapped in HTML.

You’ll get the idea if you see the workbook. I can’t figure out how to achieve the result I need in any form of formulae and don’t know the first thing about VB.

Please help!
Thanks.

[Leith Ross]

Hello garymrowe,

Welcome to the Forum!

The macro below will remove the merged cells on the "Source" worksheet. This has been added to the attached workbook.

Please Login or Register  to view this content.

To Run the Macro...
To run the macro from Excel, open the workbook, and press ALT+F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

[garymrowe]

Leith, absolutely incredible. I did not think this would be possible. Thank you so much for your help.

[snb]

or

Please Login or Register  to view this content.

[Hibernation]

Thank you so much, Leith Ross, it's really helpful