In the file I had attached, Col A has data like the way you have represented in the file you attached - only difference is that it "looks" and "behaves (for navigation)" like merged cells. And I agree with your direction of not using merged cells if data is compromised. TempSht.Delete 'deletes the temp worksheetĪpplication.Calculation = xlCalculationAutomatic Sht.Range("A1:C" & lRow).Borders.LineStyle = xlContinuous Sht.Range("A1:C" & lRow).PasteSpecial xlPasteFormats 'pastes the table back into your main sheet from temp using paste formats
Excel how to merge cells in a table code#
I = 0 'resets to 0 to allow next ISD Code to be looked at TempSht.Range("B" & Cell.Row - i & ":B" & Cell.Row).VerticalAlignment = xlCenter TempSht.Range("B" & Cell.Row - i & ":B" & Cell.Row).Merge ' merges cells in column B TempSht.Range("A" & Cell.Row - i & ":A" & Cell.Row).VerticalAlignment = xlCenter TempSht.Range("A" & Cell.Row - i & ":A" & Cell.Row).Merge 'merges cells in column A TempSht.Range("A1:C" & lRow).Value = sht.Range("A1:C" & lRow).Value 'copy the table from the original sheet to the temp sheet Sht.ShowAllData 'clears any filtering that may be applied Sht.Range("A1:C" & lRow).AutoFilter 'adds filter if no filter = "Temp" ' create a temporary worksheet to merge the cells Set sht = Worksheets("Sheet1") ' Change your sheet name as required here Is there a way to achieve the first method using VBA, without having to use PasteSpecial?Īpplication.Calculation = xlCalculationManual
But when filter is applied, only one corresponding is fetched from Column C. In this case, I am prompted with a message that warns: "Merging cells keeps the upper-left value and discards other values". The second method: I use the Merge Cells from Home Ribbon.
Also, notice that when the cells are unmerged, all underlying cells still have the data in them. When I use this method, I can apply filter in Column A (ISD Code), and it fetches all corresponding rows from Column C (State). 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. 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.
0 kommentar(er)
