Split merge cells & repeat text in split column

[SJ]

Hi,

I have a table with 5 columns and 10 rows. Row 1 is header and it
contains heading information in col 1. Col 2 & 3 are merged columns
and text is spanning across two columns. Similarly I have col 4 & col5
which are merged and header text is spanning multiple columns. I want
to count number of columns a merged cell text is spanning and then
break it down into same number of columns as being spanned and put the
header text value in each column repeatedly.
For eg: "span Text' is spanning across col2 & 3. I want to break the
spanning text into two columns and each column col2 & col3 should
contain "span text" in them.

Can anyone please help me with this issue. . Thank you

- Sandeep

 

[Doug Robbins - Word MVP on news.microsoft.com]

Use:

With Selection.Tables(1)
.Rows.Add BeforeRow:=.Rows(2)
.Cell(2, 1).Range.Text = Replace(.Cell(1, 1).Range.Text, vbCr, "")
.Cell(2, 2).Range.Text = Replace(.Cell(1, 2).Range.Text, vbCr, "")
.Cell(2, 3).Range.Text = Replace(.Cell(1, 2).Range.Text, vbCr, "")
.Cell(2, 4).Range.Text = Replace(.Cell(1, 3).Range.Text, vbCr, "")
.Cell(2, 5).Range.Text = Replace(.Cell(1, 3).Range.Text, vbCr, "")
.Cell(1, 1).Range.Select
.Split BeforeRow:=.Rows(2)
End With
Selection.Tables(1).Delete


--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP, originally posted via msnews.microsoft.com

 

[SJ]

Use:

With Selection.Tables(1)
    .Rows.Add BeforeRow:=.Rows(2)
    .Cell(2, 1).Range.Text = Replace(.Cell(1, 1).Range.Text, vbCr, "")
    .Cell(2, 2).Range.Text = Replace(.Cell(1, 2).Range.Text, vbCr, "")
    .Cell(2, 3).Range.Text = Replace(.Cell(1, 2).Range.Text, vbCr, "")
    .Cell(2, 4).Range.Text = Replace(.Cell(1, 3).Range.Text, vbCr, "")
    .Cell(2, 5).Range.Text = Replace(.Cell(1, 3).Range.Text, vbCr, "")
    .Cell(1, 1).Range.Select
    .Split BeforeRow:=.Rows(2)
End With
Selection.Tables(1).Delete

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP, originally posted via msnews.microsoft.com









- Show quoted text -

Hi Doug,

Thanks for your response. The issue I have is the tables are
dynamic. I dont know how many columns will be in a table & how many
columns a particular heading will be spanning. I need to develop
dynamic way to find out how many columns a heading is spanning and
then copy that text for each column. Please guide me. Thank you.

 

[Doug Robbins - Word MVP on news.microsoft.com]

Even in your latter posts, you still say there are "5 columns. first row has
spanning headers in the
columns 2&3, 4&5. so totally 3 cells is there in first row"

You are not going to be able to develop a macro that will handle all
possible combinations of numbers of columns and numbers of columns spanned
by merged cells in the first row.

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP, originally posted via msnews.microsoft.com

Use:

With Selection.Tables(1)
.Rows.Add BeforeRow:=.Rows(2)
.Cell(2, 1).Range.Text = Replace(.Cell(1, 1).Range.Text, vbCr, "")
.Cell(2, 2).Range.Text = Replace(.Cell(1, 2).Range.Text, vbCr, "")
.Cell(2, 3).Range.Text = Replace(.Cell(1, 2).Range.Text, vbCr, "")
.Cell(2, 4).Range.Text = Replace(.Cell(1, 3).Range.Text, vbCr, "")
.Cell(2, 5).Range.Text = Replace(.Cell(1, 3).Range.Text, vbCr, "")
.Cell(1, 1).Range.Select
.Split BeforeRow:=.Rows(2)
End With
Selection.Tables(1).Delete

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP, originally posted via msnews.microsoft.com









- Show quoted text -

Hi Doug,

Thanks for your response. The issue I have is the tables are
dynamic. I dont know how many columns will be in a table & how many
columns a particular heading will be spanning. I need to develop
dynamic way to find out how many columns a heading is spanning and
then copy that text for each column. Please guide me. Thank you.

 

[fly in programming sky]

Dim rowRange as Range
Dim secondRowRange as Range
Dim rowLen as Integer
rowRange = Selection.Tables(1).Rows(1).Range
secondRowRange = Selection.Tables(1).Row(2).Range
rowLen = rowRange.End - rowRange.Start
for index = 0 to rowRange.Cells.Count step 1
if rowRange.Cells(index).Range.End > secondRowRange.Cells(index).Range.End
- rowLen
then
rowRange.Cells(index).split(2)

I am sorry that I'm not familiar with VBA, but I hope that you can
understand what I want to show.