Split single cell into multiple rows

[Keith Smothers]

I've got a cell in my table that I want to split into
eight rows. I've got eight 'entries' in the cell,
currently divided by paragraph marks. I want each of these
entries to be in its own row.

When I do 'split cells' (1 column, 8 rows) with the 'Merge
cells before split' toggled ON or OFF, I get eight rows
with all eight entries in the first row. That must be the
merge thing.

When I do 'split cells' (8 columns, 1 row) with the 'Merge
cells before split' toggled ON or OFF, I get eight columns
with an individual entry in each column.

How come the split will place the entries in individual
columns but not individual rows?

Is there any quick way to split this cell into rows and
have the entries end up in the rows as I desire?

 

[Doug Robbins - Word MVP - DELETE UPPERCASE CHARACT]

Hi Keith,

If you run a macro containing the following code when the selection is in
the cell that you want to split, it will do what you want:

Dim newrows As Long, i As Long, myrange As Range
newrows = Selection.Cells(1).Range.Paragraphs.Count
Selection.Cells(1).Split numrows:=newrows
For i = 2 To newrows
Set myrange = Selection.Columns(1).Cells(1).Range.Paragraphs(i).Range
myrange.End = myrange.End - 1
Selection.Columns(1).Cells(i).Range.InsertBefore myrange
Next i
Set myrange = Selection.Columns(1).Cells(1).Range.Paragraphs(1).Range
myrange.End = myrange.End - 1
Selection.Columns(1).Cells(1).Range = myrange


--
Please post any further questions or followup to the newsgroups for the
benefit of others who may be interested. Unsolicited questions forwarded
directly to me will only be answered on a paid consulting basis.
Hope this helps
Doug Robbins - Word MVP

 

[Keith Smothers]

Doug,

First, thanks for the quick response. I really appreciate
your help.

I think I did what you said to do but, being a macro
newbie, I'm not sure that I got it right. At any rate,
it's not working.

I created a macro by cutting and pasting your macro
language. Here's what it looks like:

1 Dim newrows As Long, i As Long, myrange As Range
2 newrows = Selection.Cells(1).Range.Paragraphs.Count
3 Selection.Cells(1).Split numrows:=newrows
4 For i = 2 To newrows
5 Set myrange = Selection.Columns(1).Cells
1).Range.Paragraphs(i).Range
6 myrange.End = myrange.End - 1
7 Selection.Columns(1).Cells(i).Range.InsertBefore
myrange
8 Next i
9 Set myrange = Selection.Columns(1).Cells
(1).Range.Paragraphs(1).Range
10 myrange.End = myrange.End - 1
11 Selection.Columns(1).Cells(1).Range = myrange

PLEASE NOTE: The numbers at the left margin are for
reference purposes only - they are not in the macro I
created.

I select the entire cell that I want to split into rows
and then run the macro. The macro breaks the cell up into
the appropriate number of rows, but all entries are still
in the first 'new' row.

Additionally, I get an error message. The initial pop up
reads:

Microsoft Visual Basic
Run-time error '5941':
The requested member of the collection does not exist.

When I click the 'Debug' button on the above pop up, it
opens up a 'Microsoft Visual Basic - Normal [break] -
[NewMacros (Code)]' window which shows my new macro and
has line number 5 of the macro highlighted in yellow.

What did I do wrong (or not do at all)?

TIA,
Keith

 

[Doug Robbins - Word MVP - DELETE UPPERCASE CHARACT]

Hi Keith,

I can't see anything wrong with your code except for the missing ( after
Cells in line 5

5 Set myrange = Selection.Columns(1).Cells
1).Range.Paragraphs(i).Range

should be

5 Set myrange = Selection.Columns(1).Cells
(1).Range.Paragraphs(i).Range

But it would not run at all without that.

I just tested it again here and it works fine. I note however, If the cell
that you want to split is in the first column of the table, it does not
matter if you just have the selection in the cell, select the whole cell, or
select the whole row. If however the cell is not in the first column, it
will only work properly if the whole cell is NOT selected. (though it does
not give an error message if it is)

--
Please post any further questions or followup to the newsgroups for the
benefit of others who may be interested. Unsolicited questions forwarded
directly to me will only be answered on a paid consulting basis.
Hope this helps
Doug Robbins - Word MVP

Doug,

First, thanks for the quick response. I really appreciate
your help.

I think I did what you said to do but, being a macro
newbie, I'm not sure that I got it right. At any rate,
it's not working.

I created a macro by cutting and pasting your macro
language. Here's what it looks like:

1 Dim newrows As Long, i As Long, myrange As Range
2 newrows = Selection.Cells(1).Range.Paragraphs.Count
3 Selection.Cells(1).Split numrows:=newrows
4 For i = 2 To newrows
5 Set myrange = Selection.Columns(1).Cells
1).Range.Paragraphs(i).Range
6 myrange.End = myrange.End - 1
7 Selection.Columns(1).Cells(i).Range.InsertBefore
myrange
8 Next i
9 Set myrange = Selection.Columns(1).Cells
(1).Range.Paragraphs(1).Range
10 myrange.End = myrange.End - 1
11 Selection.Columns(1).Cells(1).Range = myrange

PLEASE NOTE: The numbers at the left margin are for
reference purposes only - they are not in the macro I
created.

I select the entire cell that I want to split into rows
and then run the macro. The macro breaks the cell up into
the appropriate number of rows, but all entries are still
in the first 'new' row.

Additionally, I get an error message. The initial pop up
reads:

Microsoft Visual Basic
Run-time error '5941':
The requested member of the collection does not exist.

When I click the 'Debug' button on the above pop up, it
opens up a 'Microsoft Visual Basic - Normal [break] -
[NewMacros (Code)]' window which shows my new macro and
has line number 5 of the macro highlighted in yellow.

What did I do wrong (or not do at all)?

TIA,
Keith

-----Original Message-----
Hi Keith,

If you run a macro containing the following code when the selection is in
the cell that you want to split, it will do what you want:

Dim newrows As Long, i As Long, myrange As Range
newrows = Selection.Cells(1).Range.Paragraphs.Count
Selection.Cells(1).Split numrows:=newrows
For i = 2 To newrows
Set myrange = Selection.Columns(1).Cells (1).Range.Paragraphs(i).Range
myrange.End = myrange.End - 1
Selection.Columns(1).Cells(i).Range.InsertBefore myrange
Next i
Set myrange = Selection.Columns(1).Cells (1).Range.Paragraphs(1).Range
myrange.End = myrange.End - 1
Selection.Columns(1).Cells(1).Range = myrange


--
Please post any further questions or followup to the newsgroups for the
benefit of others who may be interested. Unsolicited questions forwarded
directly to me will only be answered on a paid consulting basis.
Hope this helps
Doug Robbins - Word MVP



.

 

[Guest]

Doug,

I typoed that paren in my posting - it is in the macro.

The cells I am fighting with are in the third and fourth
(of four) columns in my table.

I actually already tried running the macro with the cursor
just sitting in the cell instead of having the entire cell
selected.

I will, however, go back and try it again.

Thanks,
Keith

-----Original Message-----
Hi Keith,

I can't see anything wrong with your code except for the missing ( after
Cells in line 5

5 Set myrange = Selection.Columns(1).Cells
1).Range.Paragraphs(i).Range

should be

5 Set myrange = Selection.Columns(1).Cells
(1).Range.Paragraphs(i).Range

But it would not run at all without that.

I just tested it again here and it works fine. I note however, If the cell
that you want to split is in the first column of the table, it does not
matter if you just have the selection in the cell, select the whole cell, or
select the whole row. If however the cell is not in the first column, it
will only work properly if the whole cell is NOT selected. (though it does
not give an error message if it is)

--
Please post any further questions or followup to the newsgroups for the
benefit of others who may be interested. Unsolicited questions forwarded
directly to me will only be answered on a paid consulting basis.
Hope this helps
Doug Robbins - Word MVP

Doug,

First, thanks for the quick response. I really appreciate
your help.

I think I did what you said to do but, being a macro
newbie, I'm not sure that I got it right. At any rate,
it's not working.

I created a macro by cutting and pasting your macro
language. Here's what it looks like:

1 Dim newrows As Long, i As Long, myrange As Range
2 newrows = Selection.Cells(1).Range.Paragraphs.Count
3 Selection.Cells(1).Split numrows:=newrows
4 For i = 2 To newrows
5 Set myrange = Selection.Columns(1).Cells
1).Range.Paragraphs(i).Range
6 myrange.End = myrange.End - 1
7 Selection.Columns(1).Cells(i).Range.InsertBefore
myrange
8 Next i
9 Set myrange = Selection.Columns(1).Cells
(1).Range.Paragraphs(1).Range
10 myrange.End = myrange.End - 1
11 Selection.Columns(1).Cells(1).Range = myrange

PLEASE NOTE: The numbers at the left margin are for
reference purposes only - they are not in the macro I
created.

I select the entire cell that I want to split into rows
and then run the macro. The macro breaks the cell up into
the appropriate number of rows, but all entries are still
in the first 'new' row.

Additionally, I get an error message. The initial pop up
reads:

Microsoft Visual Basic
Run-time error '5941':
The requested member of the collection does not exist.

When I click the 'Debug' button on the above pop up, it
opens up a 'Microsoft Visual Basic - Normal [break] -
[NewMacros (Code)]' window which shows my new macro and
has line number 5 of the macro highlighted in yellow.

What did I do wrong (or not do at all)?

TIA,
Keith

-----Original Message-----
Hi Keith,

If you run a macro containing the following code when

the
selection is in

the cell that you want to split, it will do what you want:

Dim newrows As Long, i As Long, myrange As Range
newrows = Selection.Cells(1).Range.Paragraphs.Count
Selection.Cells(1).Split numrows:=newrows
For i = 2 To newrows
Set myrange = Selection.Columns(1).Cells (1).Range.Paragraphs(i).Range
myrange.End = myrange.End - 1
Selection.Columns(1).Cells(i).Range.InsertBefore myrange
Next i
Set myrange = Selection.Columns(1).Cells (1).Range.Paragraphs(1).Range
myrange.End = myrange.End - 1
Selection.Columns(1).Cells(1).Range = myrange

consulting
basis.

Hope this helps
Doug Robbins - Word MVP
I've got a cell in my table that I want to split into
eight rows. I've got eight 'entries' in the cell,
currently divided by paragraph marks. I want each of these
entries to be in its own row.

When I do 'split cells' (1 column, 8 rows) with the 'Merge
cells before split' toggled ON or OFF, I get eight rows
with all eight entries in the first row. That must be the
merge thing.

When I do 'split cells' (8 columns, 1 row) with the 'Merge
cells before split' toggled ON or OFF, I get eight columns
with an individual entry in each column.

How come the split will place the entries in individual
columns but not individual rows?

Is there any quick way to split this cell into rows and
have the entries end up in the rows as I desire?


.

.

 

[Keith Smothers]

Doug,

I tried it again in a new table. I can get the macro to
work in the first row, but it doesn't work in any other
rows???

Keith

-----Original Message-----
Hi Keith,

I can't see anything wrong with your code except for the missing ( after
Cells in line 5

5 Set myrange = Selection.Columns(1).Cells
1).Range.Paragraphs(i).Range

should be

5 Set myrange = Selection.Columns(1).Cells
(1).Range.Paragraphs(i).Range

But it would not run at all without that.

I just tested it again here and it works fine. I note however, If the cell
that you want to split is in the first column of the table, it does not
matter if you just have the selection in the cell, select the whole cell, or
select the whole row. If however the cell is not in the first column, it
will only work properly if the whole cell is NOT selected. (though it does
not give an error message if it is)

--
Please post any further questions or followup to the newsgroups for the
benefit of others who may be interested. Unsolicited questions forwarded
directly to me will only be answered on a paid consulting basis.
Hope this helps
Doug Robbins - Word MVP

Doug,

First, thanks for the quick response. I really appreciate
your help.

I think I did what you said to do but, being a macro
newbie, I'm not sure that I got it right. At any rate,
it's not working.

I created a macro by cutting and pasting your macro
language. Here's what it looks like:

1 Dim newrows As Long, i As Long, myrange As Range
2 newrows = Selection.Cells(1).Range.Paragraphs.Count
3 Selection.Cells(1).Split numrows:=newrows
4 For i = 2 To newrows
5 Set myrange = Selection.Columns(1).Cells
1).Range.Paragraphs(i).Range
6 myrange.End = myrange.End - 1
7 Selection.Columns(1).Cells(i).Range.InsertBefore
myrange
8 Next i
9 Set myrange = Selection.Columns(1).Cells
(1).Range.Paragraphs(1).Range
10 myrange.End = myrange.End - 1
11 Selection.Columns(1).Cells(1).Range = myrange

PLEASE NOTE: The numbers at the left margin are for
reference purposes only - they are not in the macro I
created.

I select the entire cell that I want to split into rows
and then run the macro. The macro breaks the cell up into
the appropriate number of rows, but all entries are still
in the first 'new' row.

Additionally, I get an error message. The initial pop up
reads:

Microsoft Visual Basic
Run-time error '5941':
The requested member of the collection does not exist.

When I click the 'Debug' button on the above pop up, it
opens up a 'Microsoft Visual Basic - Normal [break] -
[NewMacros (Code)]' window which shows my new macro and
has line number 5 of the macro highlighted in yellow.

What did I do wrong (or not do at all)?

TIA,
Keith

-----Original Message-----
Hi Keith,

If you run a macro containing the following code when

the
selection is in

the cell that you want to split, it will do what you want:

Dim newrows As Long, i As Long, myrange As Range
newrows = Selection.Cells(1).Range.Paragraphs.Count
Selection.Cells(1).Split numrows:=newrows
For i = 2 To newrows
Set myrange = Selection.Columns(1).Cells (1).Range.Paragraphs(i).Range
myrange.End = myrange.End - 1
Selection.Columns(1).Cells(i).Range.InsertBefore myrange
Next i
Set myrange = Selection.Columns(1).Cells (1).Range.Paragraphs(1).Range
myrange.End = myrange.End - 1
Selection.Columns(1).Cells(1).Range = myrange

consulting
basis.

Hope this helps
Doug Robbins - Word MVP
I've got a cell in my table that I want to split into
eight rows. I've got eight 'entries' in the cell,
currently divided by paragraph marks. I want each of these
entries to be in its own row.

When I do 'split cells' (1 column, 8 rows) with the 'Merge
cells before split' toggled ON or OFF, I get eight rows
with all eight entries in the first row. That must be the
merge thing.

When I do 'split cells' (8 columns, 1 row) with the 'Merge
cells before split' toggled ON or OFF, I get eight columns
with an individual entry in each column.

How come the split will place the entries in individual
columns but not individual rows?

Is there any quick way to split this cell into rows and
have the entries end up in the rows as I desire?


.

.

 

[Greg Maxey]

Keith/Doug

After much trial and error, wailing and gnashing of teeth, I think I have
figured this out. The problem as I saw it was the myrange cell number had
to be connected to the row number. First I tried an input box where the
user manually inserted the row number. Then I discovered the
Selection.Informtion feature which automated the process.

Try:

Sub SplitTableRowsWithText()
Dim newrows As Long, i As Long, myrange As Range, j As Long, RowNum As Long
RowNum = Selection.Information(wdEndOfRangeRowNumber)
newrows = Selection.Cells(1).Range.Paragraphs.Count
Selection.Cells(1).Split numrows:=newrows
For i = 2 To newrows
j = i + (RowNum - 1)
Set myrange =
Selection.Columns(1).Cells(RowNum).Range.Paragraphs(i).Range
myrange.End = myrange.End - 1
Selection.Columns(1).Cells(j).Range.InsertBefore myrange
Next i
Set myrange = Selection.Columns(1).Cells(RowNum).Range.Paragraphs(1).Range
myrange.End = myrange.End - 1
Selection.Columns(1).Cells(RowNum).Range = myrange
End Sub

--
Greg Maxey
A peer in "peer to peer" support
Rockledge, FL
To e-mail, edit out the "w...spam" in (e-mail address removed)

Doug,

I tried it again in a new table. I can get the macro to
work in the first row, but it doesn't work in any other
rows???

Keith

-----Original Message-----
Hi Keith,

I can't see anything wrong with your code except for the missing (
after Cells in line 5

5 Set myrange = Selection.Columns(1).Cells
1).Range.Paragraphs(i).Range

should be

5 Set myrange = Selection.Columns(1).Cells
(1).Range.Paragraphs(i).Range

But it would not run at all without that.

I just tested it again here and it works fine. I note however, If
the cell that you want to split is in the first column of the table,
it does not matter if you just have the selection in the cell,
select the whole cell, or select the whole row. If however the cell
is not in the first column, it will only work properly if the whole
cell is NOT selected. (though it does
not give an error message if it is)

--
Please post any further questions or followup to the newsgroups for
the benefit of others who may be interested. Unsolicited questions
forwarded directly to me will only be answered on a paid consulting
basis. Hope this helps
Doug Robbins - Word MVP

Doug,

First, thanks for the quick response. I really appreciate
your help.

I think I did what you said to do but, being a macro
newbie, I'm not sure that I got it right. At any rate,
it's not working.

I created a macro by cutting and pasting your macro
language. Here's what it looks like:

1 Dim newrows As Long, i As Long, myrange As Range
2 newrows = Selection.Cells(1).Range.Paragraphs.Count
3 Selection.Cells(1).Split numrows:=newrows
4 For i = 2 To newrows
5 Set myrange = Selection.Columns(1).Cells
1).Range.Paragraphs(i).Range
6 myrange.End = myrange.End - 1
7 Selection.Columns(1).Cells(i).Range.InsertBefore
myrange
8 Next i
9 Set myrange = Selection.Columns(1).Cells
(1).Range.Paragraphs(1).Range
10 myrange.End = myrange.End - 1
11 Selection.Columns(1).Cells(1).Range = myrange

PLEASE NOTE: The numbers at the left margin are for
reference purposes only - they are not in the macro I
created.

I select the entire cell that I want to split into rows
and then run the macro. The macro breaks the cell up into
the appropriate number of rows, but all entries are still
in the first 'new' row.

Additionally, I get an error message. The initial pop up
reads:

Microsoft Visual Basic
Run-time error '5941':
The requested member of the collection does not exist.

When I click the 'Debug' button on the above pop up, it
opens up a 'Microsoft Visual Basic - Normal [break] -
[NewMacros (Code)]' window which shows my new macro and
has line number 5 of the macro highlighted in yellow.

What did I do wrong (or not do at all)?

TIA,
Keith


-----Original Message-----
Hi Keith,

If you run a macro containing the following code when the
selection is in the cell that you want to split, it will do what
you want:

Dim newrows As Long, i As Long, myrange As Range
newrows = Selection.Cells(1).Range.Paragraphs.Count
Selection.Cells(1).Split numrows:=newrows
For i = 2 To newrows
Set myrange = Selection.Columns(1).Cells
(1).Range.Paragraphs(i).Range myrange.End = myrange.End - 1
Selection.Columns(1).Cells(i).Range.InsertBefore myrange
Next i
Set myrange = Selection.Columns(1).Cells
(1).Range.Paragraphs(1).Range myrange.End = myrange.End - 1
Selection.Columns(1).Cells(1).Range = myrange


--
Please post any further questions or followup to the newsgroups
for the benefit of others who may be interested. Unsolicited
questions forwarded directly to me will only be answered on a paid
consulting basis. Hope this helps
Doug Robbins - Word MVP
I've got a cell in my table that I want to split into
eight rows. I've got eight 'entries' in the cell,
currently divided by paragraph marks. I want each of these
entries to be in its own row.

When I do 'split cells' (1 column, 8 rows) with
the 'Merge
cells before split' toggled ON or OFF, I get eight rows
with all eight entries in the first row. That must be the
merge thing.

When I do 'split cells' (8 columns, 1 row) with
the 'Merge
cells before split' toggled ON or OFF, I get eight columns
with an individual entry in each column.

How come the split will place the entries in individual
columns but not individual rows?

Is there any quick way to split this cell into rows and
have the entries end up in the rows as I desire?


.

.

 

[Doug Robbins - Word MVP - DELETE UPPERCASE CHARACT]

Hi Greg/Keith,

Yes, I had only tried it on a cell in the first row of a table.

Here's a slight simplification of our combined effort:

Dim newrows As Long, i As Long, myrange As Range, RowNum As Long
RowNum = Selection.Information(wdEndOfRangeRowNumber)
newrows = Selection.Cells(1).Range.Paragraphs.Count
Selection.Cells(1).Split numrows:=newrows
For i = 2 To newrows
Set myrange = Selection.Cells(1).Range.Paragraphs(i).Range
myrange.End = myrange.End - 1
Selection.Columns(1).Cells(i + RowNum - 1).Range.InsertBefore myrange
Next i
Set myrange = Selection.Cells(1).Range.Paragraphs(1).Range
myrange.End = myrange.End - 1
Selection.Cells(1).Range = myrange


--
Please post any further questions or followup to the newsgroups for the
benefit of others who may be interested. Unsolicited questions forwarded
directly to me will only be answered on a paid consulting basis.
Hope this helps
Doug Robbins - Word MVP

Keith/Doug

After much trial and error, wailing and gnashing of teeth, I think I have
figured this out. The problem as I saw it was the myrange cell number had
to be connected to the row number. First I tried an input box where the
user manually inserted the row number. Then I discovered the
Selection.Informtion feature which automated the process.

Try:

Sub SplitTableRowsWithText()
Dim newrows As Long, i As Long, myrange As Range, j As Long, RowNum As
Long
RowNum = Selection.Information(wdEndOfRangeRowNumber)
newrows = Selection.Cells(1).Range.Paragraphs.Count
Selection.Cells(1).Split numrows:=newrows
For i = 2 To newrows
j = i + (RowNum - 1)
Set myrange =
Selection.Columns(1).Cells(RowNum).Range.Paragraphs(i).Range
myrange.End = myrange.End - 1
Selection.Columns(1).Cells(j).Range.InsertBefore myrange
Next i
Set myrange = Selection.Columns(1).Cells(RowNum).Range.Paragraphs(1).Range
myrange.End = myrange.End - 1
Selection.Columns(1).Cells(RowNum).Range = myrange
End Sub

--
Greg Maxey
A peer in "peer to peer" support
Rockledge, FL
To e-mail, edit out the "w...spam" in >

Doug,

I tried it again in a new table. I can get the macro to
work in the first row, but it doesn't work in any other
rows???

Keith

-----Original Message-----
Hi Keith,

I can't see anything wrong with your code except for the missing (
after Cells in line 5

5 Set myrange = Selection.Columns(1).Cells
1).Range.Paragraphs(i).Range

should be

5 Set myrange = Selection.Columns(1).Cells
(1).Range.Paragraphs(i).Range

But it would not run at all without that.

I just tested it again here and it works fine. I note however, If
the cell that you want to split is in the first column of the table,
it does not matter if you just have the selection in the cell,
select the whole cell, or select the whole row. If however the cell
is not in the first column, it will only work properly if the whole
cell is NOT selected. (though it does
not give an error message if it is)

--
Please post any further questions or followup to the newsgroups for
the benefit of others who may be interested. Unsolicited questions
forwarded directly to me will only be answered on a paid consulting
basis. Hope this helps
Doug Robbins - Word MVP
Doug,

First, thanks for the quick response. I really appreciate
your help.

I think I did what you said to do but, being a macro
newbie, I'm not sure that I got it right. At any rate,
it's not working.

I created a macro by cutting and pasting your macro
language. Here's what it looks like:

1 Dim newrows As Long, i As Long, myrange As Range
2 newrows = Selection.Cells(1).Range.Paragraphs.Count
3 Selection.Cells(1).Split numrows:=newrows
4 For i = 2 To newrows
5 Set myrange = Selection.Columns(1).Cells
1).Range.Paragraphs(i).Range
6 myrange.End = myrange.End - 1
7 Selection.Columns(1).Cells(i).Range.InsertBefore
myrange
8 Next i
9 Set myrange = Selection.Columns(1).Cells
(1).Range.Paragraphs(1).Range
10 myrange.End = myrange.End - 1
11 Selection.Columns(1).Cells(1).Range = myrange

PLEASE NOTE: The numbers at the left margin are for
reference purposes only - they are not in the macro I
created.

I select the entire cell that I want to split into rows
and then run the macro. The macro breaks the cell up into
the appropriate number of rows, but all entries are still
in the first 'new' row.

Additionally, I get an error message. The initial pop up
reads:

Microsoft Visual Basic
Run-time error '5941':
The requested member of the collection does not exist.

When I click the 'Debug' button on the above pop up, it
opens up a 'Microsoft Visual Basic - Normal [break] -
[NewMacros (Code)]' window which shows my new macro and
has line number 5 of the macro highlighted in yellow.

What did I do wrong (or not do at all)?

TIA,
Keith


-----Original Message-----
Hi Keith,

If you run a macro containing the following code when the
selection is in the cell that you want to split, it will do what
you want:

Dim newrows As Long, i As Long, myrange As Range
newrows = Selection.Cells(1).Range.Paragraphs.Count
Selection.Cells(1).Split numrows:=newrows
For i = 2 To newrows
Set myrange = Selection.Columns(1).Cells
(1).Range.Paragraphs(i).Range myrange.End = myrange.End - 1
Selection.Columns(1).Cells(i).Range.InsertBefore myrange
Next i
Set myrange = Selection.Columns(1).Cells
(1).Range.Paragraphs(1).Range myrange.End = myrange.End - 1
Selection.Columns(1).Cells(1).Range = myrange


--
Please post any further questions or followup to the newsgroups
for the benefit of others who may be interested. Unsolicited
questions forwarded directly to me will only be answered on a paid
consulting basis. Hope this helps
Doug Robbins - Word MVP
I've got a cell in my table that I want to split into
eight rows. I've got eight 'entries' in the cell,
currently divided by paragraph marks. I want each of these
entries to be in its own row.

When I do 'split cells' (1 column, 8 rows) with
the 'Merge
cells before split' toggled ON or OFF, I get eight rows
with all eight entries in the first row. That must be the
merge thing.

When I do 'split cells' (8 columns, 1 row) with
the 'Merge
cells before split' toggled ON or OFF, I get eight columns
with an individual entry in each column.

How come the split will place the entries in individual
columns but not individual rows?

Is there any quick way to split this cell into rows and
have the entries end up in the rows as I desire?


.



.

 

[Keith Smothers]

Doug/Greg -

That's it (as you already know)!

I sincerely appreciate both of your help. Greg, I'm sorry
about the teeth gnashing - I hope no long term damage
occurred.

Thanks again!
Keith

-----Original Message-----
Hi Greg/Keith,

Yes, I had only tried it on a cell in the first row of a table.

Here's a slight simplification of our combined effort:

Dim newrows As Long, i As Long, myrange As Range, RowNum As Long
RowNum = Selection.Information(wdEndOfRangeRowNumber)
newrows = Selection.Cells(1).Range.Paragraphs.Count
Selection.Cells(1).Split numrows:=newrows
For i = 2 To newrows
Set myrange = Selection.Cells(1).Range.Paragraphs (i).Range
myrange.End = myrange.End - 1
Selection.Columns(1).Cells(i + RowNum - 1).Range.InsertBefore myrange
Next i
Set myrange = Selection.Cells(1).Range.Paragraphs(1).Range
myrange.End = myrange.End - 1
Selection.Cells(1).Range = myrange


--
Please post any further questions or followup to the newsgroups for the
benefit of others who may be interested. Unsolicited questions forwarded
directly to me will only be answered on a paid consulting basis.
Hope this helps
Doug Robbins - Word MVP

Keith/Doug

After much trial and error, wailing and gnashing of teeth, I think I have
figured this out. The problem as I saw it was the myrange cell number had
to be connected to the row number. First I tried an input box where the
user manually inserted the row number. Then I discovered the
Selection.Informtion feature which automated the process.

Try:

Sub SplitTableRowsWithText()
Dim newrows As Long, i As Long, myrange As Range, j As Long, RowNum As
Long
RowNum = Selection.Information(wdEndOfRangeRowNumber)
newrows = Selection.Cells(1).Range.Paragraphs.Count
Selection.Cells(1).Split numrows:=newrows
For i = 2 To newrows
j = i + (RowNum - 1)
Set myrange =
Selection.Columns(1).Cells(RowNum).Range.Paragraphs (i).Range
myrange.End = myrange.End - 1
Selection.Columns(1).Cells(j).Range.InsertBefore myrange
Next i
Set myrange = Selection.Columns(1).Cells (RowNum).Range.Paragraphs(1).Range
myrange.End = myrange.End - 1
Selection.Columns(1).Cells(RowNum).Range = myrange
End Sub

--
Greg Maxey
A peer in "peer to peer" support
Rockledge, FL
To e-mail, edit out the "w...spam" in >

Doug,

I tried it again in a new table. I can get the macro to
work in the first row, but it doesn't work in any other
rows???

Keith

-----Original Message-----
Hi Keith,

I can't see anything wrong with your code except for the missing (
after Cells in line 5

5 Set myrange = Selection.Columns(1).Cells
1).Range.Paragraphs(i).Range

should be

5 Set myrange = Selection.Columns(1).Cells
(1).Range.Paragraphs(i).Range

But it would not run at all without that.

I just tested it again here and it works fine. I note however, If
the cell that you want to split is in the first column of the table,
it does not matter if you just have the selection in the cell,
select the whole cell, or select the whole row. If however the cell
is not in the first column, it will only work properly if the whole
cell is NOT
selected. (though it does
not give an error message if it is)

--
Please post any further questions or followup to the newsgroups for
the benefit of others who may be interested. Unsolicited questions
forwarded directly to me will only be answered on a paid consulting
basis. Hope this helps
Doug Robbins - Word MVP
Doug,

First, thanks for the quick response. I really appreciate
your help.

I think I did what you said to do but, being a macro
newbie, I'm not sure that I got it right. At any rate,
it's not working.

I created a macro by cutting and pasting your macro
language. Here's what it looks like:

1 Dim newrows As Long, i As Long, myrange As Range
2 newrows = Selection.Cells (1).Range.Paragraphs.Count
3 Selection.Cells(1).Split numrows:=newrows
4 For i = 2 To newrows
5 Set myrange = Selection.Columns(1).Cells
1).Range.Paragraphs(i).Range
6 myrange.End = myrange.End - 1
7 Selection.Columns(1).Cells (i).Range.InsertBefore
myrange
8 Next i
9 Set myrange = Selection.Columns(1).Cells
(1).Range.Paragraphs(1).Range
10 myrange.End = myrange.End - 1
11 Selection.Columns(1).Cells(1).Range = myrange

PLEASE NOTE: The numbers at the left margin are for
reference purposes only - they are not in the macro I
created.

I select the entire cell that I want to split into rows
and then run the macro. The macro breaks the cell up into
the appropriate number of rows, but all entries are still
in the first 'new' row.

Additionally, I get an error message. The initial pop up
reads:

Microsoft Visual Basic
Run-time error '5941':
The requested member of the collection does not exist.

When I click the 'Debug' button on the above pop up, it
opens up a 'Microsoft Visual Basic - Normal [break] -
[NewMacros (Code)]' window which shows my new macro and
has line number 5 of the macro highlighted in yellow.

What did I do wrong (or not do at all)?

TIA,
Keith


-----Original Message-----
Hi Keith,

If you run a macro containing the following code when the
selection is in the cell that you want to split, it will do what
you want:

Dim newrows As Long, i As Long, myrange As Range
newrows = Selection.Cells(1).Range.Paragraphs.Count
Selection.Cells(1).Split numrows:=newrows
For i = 2 To newrows
Set myrange = Selection.Columns(1).Cells
(1).Range.Paragraphs(i).Range myrange.End = myrange.End - 1
Selection.Columns(1).Cells(i).Range.InsertBefore myrange
Next i
Set myrange = Selection.Columns(1).Cells
(1).Range.Paragraphs(1).Range myrange.End = myrange.End - 1
Selection.Columns(1).Cells(1).Range = myrange


--
Please post any further questions or followup to the newsgroups
for the benefit of others who may be interested. Unsolicited
questions forwarded directly to me will only be answered on a paid
consulting basis. Hope this helps
Doug Robbins - Word MVP
I've got a cell in my table that I want to split into
eight rows. I've got eight 'entries' in the cell,
currently divided by paragraph marks. I want each of these
entries to be in its own row.

When I do 'split cells' (1 column, 8 rows) with
the 'Merge
cells before split' toggled ON or OFF, I get eight rows
with all eight entries in the first row. That must be the
merge thing.

When I do 'split cells' (8 columns, 1 row) with
the 'Merge
cells before split' toggled ON or OFF, I get eight columns
with an individual entry in each column.

How come the split will place the entries in individual
columns but not individual rows?

Is there any quick way to split this cell into rows and
have the entries end up in the rows as I desire?


.



.

.