VBA to merge cells with identical content

**juangar1992** · 06-27-2013, 11:49 AM

Hi everyone! I am new to the forum and I'd really appreciate your help. I have a long spreadsheet, and I want to merge the cells that have identical text on column A. Can anybody help me with this macro?

Thanks!

**tigeravatar** · 06-27-2013, 12:07 PM

juangar1992,

Welcome to the forum!
Generally speaking, we're going to tell you that merging cells is always a bad idea and to avoid it at all costs. Having that duplicated data can make working with tables much easier than if you merged the cells. For example, maybe a pivot table is all you really need.

What is your end goal? and if necessary, can you post a sample workbook also?

**juangar1992** · 06-27-2013, 02:19 PM

Thank you for responding so quickly! It seems a pivot table suits even better than what I had in mind. However, one of my columns is a hyperlink to another file and for some reason the pivot table will not accept this column. Is there anything I could do to include a hyperlink in my pivot table?

Thanks!

**tigeravatar** · 06-27-2013, 02:35 PM

Unfortunately, active hyperlinks are not supported in PivotTable cells. The hyperlink is treated as text, but you cannot follow the hyperlink to a Web page or document.

**juangar1992** · 06-27-2013, 04:03 PM

Then could I use other functions to refer back to the hyperlink? Maybe something to do with OFFSET and SEARCH functions? Like creating a column concatenating all of the criteria for that specific cell in the pivot table, then using the search function on another sheet and finally using offset to get a hyperlink next to the pivot table?

Maybe something like

if(isnumber(search(concatenate($B$2,A5),Sheet4!$F$2:$F$1582)),offset(isnumber(search(concatenate($B$2,A5),Sheet4!F2:F1582)),0,-1),0)

*column F on sheet 4 contains the concatenated text (B2 + A5)*

This doesn't work though