Results 1 to 9 of 9
  1. #1
    Salan
    Guest

    Transposing tables

    Is there a way to transpose the rows and columns in a Word table? I sometimes use Excel's Paste Special command to do this, but I lose formatting. I've also written VBA macros in Word to do this, but sometimes the formatting is off (especially highlighting and hidden text).

  2. #2
    5 Star Lounger
    Join Date
    Jul 2003
    Location
    USA
    Posts
    728
    Thanks
    7
    Thanked 2 Times in 2 Posts

    Re: Transposing tables

    Did you try: Table/Select (Row or Column), then go back to Table/Insert (row/column)? (Can also do these steps from keyboard)
    Hope this helps.

  3. #3
    Salan
    Guest

    Re: Transposing tables

    Thanks for replying, but I need more information to understand your suggestion.
    I'm trying to take an existing table and turn the columns into rows and the rows into columns. For example, imagine a table with two rows and two columns. Row 1 contains cells A and B, and Row 2 contains C and D. I want to transpose the table so that Row 1 contains A and C and Row 2 contains B and D. The relationships are all the same; just the columns and rows are reversed.

  4. #4
    Super Moderator
    Join Date
    Dec 2000
    Location
    New York, NY
    Posts
    2,970
    Thanks
    3
    Thanked 29 Times in 27 Posts

    Re: Transposing tables

    Hi,

    This does sound like a task for a macro.
    You mentioned you already have one, but it can lose formatting.
    Would you be willing to post the code? - maybe with some tweaking it can do what you need.

    Gary

  5. #5
    Salan
    Guest

    Re: Transposing tables

    I'm a bit embarrassed because it's probably terrible code --I'm a hack. But thanks for the offer.

    The problem effect was that some italiced text lost its italics and vice versa. The same effect occurred with highlighting and hidden text.

    This macro is specific to a certain table size. It takes the data from 5 columns in row 6 and moves it to the second column of rows 1 to 5. You have to cut off the right two characters of every cell because of the cell end marker.

    Sub PM_TableMoveTextBottomRowtoSecondColumn()
    ttlTables = ActiveDocument.Tables.Count
    For X = 1 To ttlTables
    If ActiveDocument.Tables(X).Rows.Count <> 6 Then
    Exit Sub
    Else
    'Get the text from the last row
    ActiveDocument.Tables(X).Cell(6, 1).Select
    s1 = Selection.Text
    s1 = Left(s1, (Len(s1) - 2))
    ActiveDocument.Tables(X).Cell(6, 2).Select
    s2 = Selection.Text
    s2 = Left(s2, (Len(s2) - 2))
    ActiveDocument.Tables(X).Cell(6, 3).Select
    s3 = Selection.Text
    s3 = Left(s3, (Len(s3) - 2))
    ActiveDocument.Tables(X).Cell(6, 4).Select
    s4 = Selection.Text
    s4 = Left(s4, (Len(s4) - 2))
    ActiveDocument.Tables(X).Cell(6, 5).Select
    s5 = Selection.Text
    s5 = Left(s5, (Len(s5) - 2))
    'Put the text into the second column
    ActiveDocument.Tables(X).Cell(1, 2).Select
    Selection.Text = s1
    ActiveDocument.Tables(X).Cell(2, 2).Select
    Selection.Text = s2
    ActiveDocument.Tables(X).Cell(3, 2).Select
    Selection.Text = s3
    ActiveDocument.Tables(X).Cell(4, 2).Select
    Selection.Text = s4
    ActiveDocument.Tables(X).Cell(5, 2).Select
    Selection.Text = s5
    End If
    Next X
    End Sub

  6. #6
    Super Moderator
    Join Date
    Dec 2000
    Location
    New York, NY
    Posts
    2,970
    Thanks
    3
    Thanked 29 Times in 27 Posts

    Re: Transposing tables

    OK, I see what you're doing, and this is not bad for a hack![img]/w3timages/icons/smile.gif[/img]

    Using a string variable to shuttle the text from one location to another is the most sensible way to do it.
    The problem is that storing the text to a string variable will lose the formatting as you say.

    I can think of two workarounds, both of which would be considerably more complicated to code than what you're currently doing:

    - store all of the formatting details for each character in each cell's text - create arrays to store each possible formatting property. I had to do this once for a table reformatting macro, and that is a big chore - not recommended.

    - rather than use text strings to shuttle the text, use copy/paste. To effect this, you'd need to copy table(x) and paste the copy (table(x 1)) immediately after table(x). Delete all the text from table(x 1). Then for each cell in table(x), copy the contents, go to the appropriate cell in table(x 1) and paste. Then loop through each cell in table(x 1) and delete the extra paragraph mark that comes in when you paste. Finally, go back to table(x) and delete it, leaving what was formerly table(x 1) as your newly-reformatted table(x).

    This second one is definitely a lot more approachable to code.
    (And maybe there's a third or fourth way that's even easier).

    Note: read "table(x 1)" as "table(x plus 1)" - can't get the plus symbol to show in my post.

  7. #7
    Super Moderator
    Join Date
    Dec 2000
    Location
    New York, NY
    Posts
    2,970
    Thanks
    3
    Thanked 29 Times in 27 Posts

    Re: Transposing tables

    Here's a macro along the lines of strategy #2 in my last post.
    Note that this version is designed to work only with tables that have the same number of rows and columns - it could be changed to accommodate non-symmetric tables, though.

    <pre>Sub TableConvertRowsToColumns()
    'Gary Frieder January 2001
    'Purpose: For each table in document, swap the rows and columns
    'Warnings:
    '(1) tables must have equal numbers of columns and rows
    '(2) tables cannot contain any merged cells
    '(3) error handling has not been added
    Dim objTbls As Tables
    Dim objOrigTbl As Table
    Dim objNewTbl As Table
    Dim lngTblCt As Long
    Dim lngRowCt As Long
    Dim lngColCt As Long
    Dim n As Long
    Dim r As Long
    Dim c As Long
    Dim objNewCell As Cell
    Dim rngTmpP As Range

    Application.ScreenUpdating = False
    Set objTbls = ActiveDocument.Tables
    lngTblCt = objTbls.Count

    For n = 1 To lngTblCt
    Set objOrigTbl = objTbls(n)
    lngRowCt = objOrigTbl.Rows.Count
    lngColCt = objOrigTbl.Columns.Count
    With objOrigTbl.Range
    .Select
    .Copy
    With Selection
    .Collapse wdCollapseEnd
    .TypeParagraph
    Set rngTmpP = .Paragraphs(1).Range
    .Paste
    End With
    End With

    Set objNewTbl = objTbls(n + 1)
    For Each objNewCell In objNewTbl.Range.Cells
    With objNewCell.Range
    .ParagraphFormat.Reset
    .Font.Reset
    .Text = ""
    End With
    Next objNewCell
    '****************
    'loop through each row in original table:
    For r = 1 To lngRowCt
    'loop through each column in each row:
    For c = 1 To lngColCt
    objOrigTbl.Cell(r, c).Range.Copy
    objNewTbl.Cell(c, r).Range.Paste
    Next c
    Next r
    objOrigTbl.Select
    With Selection
    .MoveRight Extend:=True
    .Delete
    .Collapse
    End With
    Next n

    Application.ScreenUpdating = True
    End Sub
    </pre>

    Gary

  8. #8
    Super Moderator
    Join Date
    Dec 2000
    Location
    New York, NY
    Posts
    2,970
    Thanks
    3
    Thanked 29 Times in 27 Posts

    Re: Transposing tables - revised

    <P ID="edit"><FONT SIZE=-1>Edited by Gary Frieder on 01/01/07 09:16.</FONT></P>This revised version will work for tables where the number of rows and columns are not the same:

    <pre>Sub TableConvertRowsToColumnsRevised()
    'Gary Frieder January 2001
    'Purpose: For each table in document, swap the rows and columns
    ' Number of rows and columns can be different
    'Warnings:
    ' (1) Tables can not contain merged cells
    ' (2) Error handling has not been added
    Dim objTbls As Tables
    Dim objOrigTbl As Table
    Dim objNewTbl As Table
    Dim lngTblCt As Long
    Dim lngRowCt As Long
    Dim lngColCt As Long
    Dim n As Long
    Dim r As Long
    Dim c As Long
    Dim objNewCell As Cell

    Application.ScreenUpdating = False
    Set objTbls = ActiveDocument.Tables
    lngTblCt = objTbls.Count

    For n = 1 To lngTblCt
    Set objOrigTbl = objTbls(n)
    lngRowCt = objOrigTbl.Rows.Count
    lngColCt = objOrigTbl.Columns.Count
    With objOrigTbl.Range
    .Select
    With Selection
    .Collapse wdCollapseEnd
    .TypeParagraph
    'Add table, swap number of rows and columns:
    objTbls.Add Range:=Selection.Range, _
    NumRows:=lngColCt, _
    NumColumns:=lngRowCt
    End With
    End With

    Set objNewTbl = objTbls(n 1)
    '****************
    'loop through each row in original table:
    For r = 1 To lngRowCt
    'loop through each column in each row:
    For c = 1 To lngColCt
    objOrigTbl.Cell(r, c).Range.Copy
    objNewTbl.Cell(c, r).Range.Paste
    Next c
    Next r
    objOrigTbl.Select
    With Selection
    .MoveRight Extend:=True
    .Delete
    .Collapse
    End With
    Next n

    Application.ScreenUpdating = True
    End Sub
    </pre>

    Note: line that refers to objTbls(n 1), means to have a plus symbol between the n and the 1, that is (n plus symbol 1).

    Gary

  9. #9
    Salan
    Guest

    Re: Transposing tables

    Your ideas are great. Thanks so much. I'll give them a shot.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •