Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unmerge cells and fill duplicate data
I have a report that contains merged cells. I need to get rid of the merged
cells and then have the data from the first row repeated in the blank rows. I found a macro to umerge all the cells (Cells.MergeCells = False) so far so good! But now I need to repeat the data. I have two columns in the sheet that will need to be unmerged and have the data repeated. The existing workbook looks like this, with the cells for W, X, Y, and Z merged so they span one or more rows, it varies: X Y 1 2 3 4 5 6 Z W 7 8 9 1 2 3 I need it like this, so I can successfully filter: X Y 1 2 3 X Y 4 5 6 Z W 7 8 9 Z W 1 2 3 TIA for any help! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unmerge cells and fill duplicate data
Try something like this
Option Explicit Sub Test() Dim r As Excel.Range Dim r1 As Excel.Range Dim myR As Excel.Range For Each r In ActiveSheet.UsedRange Debug.Print r.Address, r.Count If r.MergeArea.Count 1 Then Set myR = r.MergeArea r.UnMerge For Each r1 In myR r1.Value2 = myR.Value2 Next r1 End If Next r End Sub HTH, Barb Reinhardt "Dawn" wrote: I have a report that contains merged cells. I need to get rid of the merged cells and then have the data from the first row repeated in the blank rows. I found a macro to umerge all the cells (Cells.MergeCells = False) so far so good! But now I need to repeat the data. I have two columns in the sheet that will need to be unmerged and have the data repeated. The existing workbook looks like this, with the cells for W, X, Y, and Z merged so they span one or more rows, it varies: X Y 1 2 3 4 5 6 Z W 7 8 9 1 2 3 I need it like this, so I can successfully filter: X Y 1 2 3 X Y 4 5 6 Z W 7 8 9 Z W 1 2 3 TIA for any help! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unmerge cells and fill duplicate data
Is the 4, 5, & 6 under the 1, 2, & 3? If so, and if you want to fill the
blanks below x & Y, look at this: http://www.contextures.com/xlDataEntry02.html If that doesn't work, please post back with a little more info. Thanks, Ryan-- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "Dawn" wrote: I have a report that contains merged cells. I need to get rid of the merged cells and then have the data from the first row repeated in the blank rows. I found a macro to umerge all the cells (Cells.MergeCells = False) so far so good! But now I need to repeat the data. I have two columns in the sheet that will need to be unmerged and have the data repeated. The existing workbook looks like this, with the cells for W, X, Y, and Z merged so they span one or more rows, it varies: X Y 1 2 3 4 5 6 Z W 7 8 9 1 2 3 I need it like this, so I can successfully filter: X Y 1 2 3 X Y 4 5 6 Z W 7 8 9 Z W 1 2 3 TIA for any help! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unmerge cells and fill duplicate data
This is AWESOME! Thank you so much for saving me hours of time! I've always
wondered how to do this. GES "ryguy7272" wrote: Is the 4, 5, & 6 under the 1, 2, & 3? If so, and if you want to fill the blanks below x & Y, look at this: http://www.contextures.com/xlDataEntry02.html If that doesn't work, please post back with a little more info. Thanks, Ryan-- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "Dawn" wrote: I have a report that contains merged cells. I need to get rid of the merged cells and then have the data from the first row repeated in the blank rows. I found a macro to umerge all the cells (Cells.MergeCells = False) so far so good! But now I need to repeat the data. I have two columns in the sheet that will need to be unmerged and have the data repeated. The existing workbook looks like this, with the cells for W, X, Y, and Z merged so they span one or more rows, it varies: X Y 1 2 3 4 5 6 Z W 7 8 9 1 2 3 I need it like this, so I can successfully filter: X Y 1 2 3 X Y 4 5 6 Z W 7 8 9 Z W 1 2 3 TIA for any help! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unmerge cells and fill duplicate data
Add another column (B) and then add this function to fill in everything until another entry for the merged cell in col A:
=IF(A2=0,B1,A2). This assumes you have one header. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unmerge cells and fill duplicate data
On Sunday, January 13, 2013 11:28:20 PM UTC+2, wrote:
Add another column (B) and then add this function to fill in everything until another entry for the merged cell in col A: =IF(A2=0,B1,A2). This assumes you have one header. For two or more rows, this will not work. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Select Merged Cells and Unmerge Spread Merge Data To All Cells | Excel Programming | |||
MACRO - Unmerge cells and delete blank columns, leaving data colum | Excel Programming | |||
Unmerge cells and place contents of first cell in all cells | Excel Programming | |||
How do you unmerge cells? | Excel Discussion (Misc queries) | |||
To safety merge cells without data destroyed, and smart unmerge! | Excel Discussion (Misc queries) |