The need: I want to merge some cells with same data in a column so it looks more organized, at the same time I should be able to apply a filter on that column that can fetch all rows from other columns corresponding to merged cells. See two methods shown in attached GIF.
The first method: I am merging cells using format painter by copying format of another set of merged cells from Column B to Column A. When I use this method, I can apply filter in Column A (ISD Code), and it fetches all corresponding rows from Column C (State). Also, notice that when the cells are unmerged, all underlying cells still have the data in them.
The second method: I use the Merge Cells from Home Ribbon. In this case, I am prompted with a message that warns: "Merging cells keeps the upper-left value and discards other values". After clicking OK, the cells are merged. But when filter is applied, only one corresponding is fetched from Column C.
Is there a way to achieve the first method using VBA, without having to use PasteSpecial?
Thanks in advance.