VBA Word Table put a string into multiple cells in a table

Posted on 2011-03-06
Last Modified: 2012-05-11
I am working with Word (2010) and have a table with either a selection or a range that includes multiple cells. I want to put a string into each of the selected cells (which will wipe out the contents of those cells). Can I do that with one command or do I have to locate to each cell to paste in the string.
     As always, many thanks for the help.

     John Robin (Allen)
     Priddis, Alberta, Canada
Question by:JohnRobinAllen
  • 6
  • 4
LVL 76

Expert Comment

ID: 35048032
You can copy and paste contiguous cells in a row or in a column and paste then into another table.

If you have a string and select a number pf columns, Word will split the string into words and will put each word into each column. If more than one row is selected, the word in each cell will be repeated in all the selected cells in the same column.
LVL 76

Expert Comment

ID: 35048055
You can do it in VBA, e.g.


Author Comment

ID: 35048128
My problem is that I do not have the string in memory, and it does not appear in the table such that I could copy it into memory.
     Even if I somehow got the string into memory, would "Activedocument.Tables(1).Rows(1).Range.Paste" be able to refer to and paste into a contiguous string of cells?

     Background information: I have a table of vocabulary words and translations with a new word in each row. A user will select a contiguous section of rows to study. I then want to hide all the non-selected rows above and below the desired selection of rows. The table has a general purpose column for sorting, so I want to put an "X" into each cell in that column in rows the user wants to ignore for the moment. I can then sort the table on that column to put the undesired cells at the bottom, and then I'll delete the "X"s and hide those rows.
    I have to have these rows at the bottom of the table because each row in the table has an automatically-generated sequence number. If there are hidden rows above the unhidden rows, the numbering will be out of sequence and will start with the first number after any hidden rows above.
    A possible work-around would be to hide rows above and below the originally selected rows and then restart the numbering on the visible rows. If the visible rows were then sorted (as the program repeatedly does), then would I not have also to program a new starting number after each sort? I think my sorting solution for hiding rows is the cleaner and more elegant solution, but someone with more experience than I may think differently.

    Thanks for your help. I hope we can solve this problem.

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.


Accepted Solution

hirstmg earned 250 total points
ID: 35049201
Hi John
if it is a simple table something like this seems to work. When you select a few cells in a table over multiple rows the selection on screen looks right but the selection range of cells in vba includes the ones in the outer and inner columns you haven't selected. So finding the first and last cell and getting their column value and then only updating the cells within those columns ...

Sub UpdateSelectedCells()
    Call TableCellUpdater("fred")
End Sub

Sub TableCellUpdater(str As String)
'Works for a simple table
    Dim cell As cell
    Dim cells As cells
    Set cells = Selection.Range.cells
    Dim cFirst, cLast As Integer
    cFirst = Selection.Range.cells(1).Column.Index
    cLast = cells(cells.Count).Column.Index
    For Each cell In cells
        If (cell.Column.Index >= cFirst) And (cell.Column.Index <= cLast) Then _
        cell.Range.Text = str
End Sub
LVL 76

Assisted Solution

GrahamSkan earned 250 total points
ID: 35049652
It's not entirely clear how the question relates to your requirement.

I would be inclined to cut each x row and to paste them into a separate table. It can be a bit difficult to accurately paste rows at the end of a table.

Sub MoveRows()
    Dim rw As Row
    Dim tbl As Table
    Dim rng As Range
    Dim tmptable As Table
    Set tbl = ActiveDocument.Tables(1)
    Set rng = tbl.Range
    rng.Collapse wdCollapseEnd
    rng.Collapse wdCollapseEnd
    Set bmk = ActiveDocument.Bookmarks.Add("XXXX", rng)
    For Each rw In tbl.Rows
        If Left(rw.Cells(1).Range.Text, 1) = "x" Then
            Set rng = ActiveDocument.Bookmarks("XXXX").Range
            rng.Collapse wdCollapseEnd
        Set tmptable = rng.Tables(1)
        End If
    Next rw
    tmptable.Range.Font.Hidden = True
End Sub

Open in new window


Author Comment

ID: 35059152
Both solutions helped. I will be commenting on them further in about an hour.
Sub HideColumnsNotSelected()
'   If user has selected cells in more than one row in a table, this sub hides the other rows so that the user can work with
'   what she selected. The table has visible data in the first three columns; the remaining columns should remain hidden
Dim Msg As String
Dim i As Integer
Dim StartRow As Integer
Dim EndRow As Integer
Dim LastRow As Integer
Dim tbl As Table

    '   Set some variables
    Set tbl = ActiveDocument.Tables(1)
    LastRow = tbl.Rows.Count
    StartRow = Selection.Information(wdStartOfRangeRowNumber)
    EndRow = Selection.Information(wdEndOfRangeRowNumber)
    '   Check that the selection is inside a table
    If Selection.Information(wdWithInTable) = False Then
        Exit Sub
    End If
    If EndRow - StartRow <= 0 Then   '   Selection is in a table. Are at least two rows selected?
        Msg = "Nothing selected. Nothing changed"
        MsgBox Msg, vbOKOnly
        Exit Sub
        '   User has selected cells in at least two rows, so we can get to work.
        Application.ScreenUpdating = False   '   Hide the temporary changes we are about to make
        tbl.Select                                        '   Unhide all hidden rows and columns
        Selection.Font.Hidden = False           '   Now everything in the table is visible, but we do not want cols 4-8 visible.
        '   Hide columns 4 to 8. They contain numbers used in sorting but not needed now
        Selection.MoveRight Unit:=wdCharacter, Count:=4, Extend:=wdExtend
        Selection.Font.Hidden = True
        'Delete whatever may be in column 9
        Selection.Delete Unit:=wdCharacter, Count:=1
        '   Mark then ninth cell in each row that is not in what the user had selected
        With ActiveDocument
                For i = 1 To StartRow - 1
                    tbl.cell(i, 9).Range.Text = "X"
                Next i
                For i = EndRow + 1 To LastRow
                    tbl.cell(i, 9).Range.Text = "X"
                Next i
        End With
        '   The following sort moves all the unwanted rows to the bottom
        Selection.Sort ExcludeHeader:=False, _
            FieldNumber:="Column 9", SortFieldType:=wdSortFieldAlphanumeric, SortOrder:=wdSortOrderAscending
        ActiveDocument.Tables(1).Columns(9).Select      '   We don't need the info in column 9 anymore.
        Selection.Delete Unit:=wdCharacter, Count:=1    '   Info deleted
        ActiveDocument.Tables(1).Columns(9).Select      '   Next, hide the column
        Selection.Font.Hidden = True                            '   Column invisible
        tbl.Rows(EndRow - StartRow + 2).Select           '   Calculate location of the first unwanted row, then select it
        Selection.EndKey Unit:=wdStory, Extend:=wdExtend      '   Extend selection to the bottom row
        Selection.Font.Hidden = True                                        '   Hide those unwanted rows.
    End If
End Sub

Open in new window


Author Comment

ID: 35059162
    Hirstmg’s comments are valuable in that they show how tables work. One simply cannot do what I wanted to do: paste the same text into a certain column when the rows are selected. I had to revise his code a bit to ensure that my string would be pasted in column 9 in my table:
For Each cell In cells
        If (cell.Column.Index = 9) Then cell.Range.Text = str
Since one has to do that with, say, 500 rows with 10 columns, that means the computer has to make up to 5,000 checks to insert 500 “X” marks in column 9. That being the case, it seems wiser to go through the table by one row rather than one cell at a time.
I have attached some revised code based on Hirstmg’s suggestion. It works with the document “Sample vocab data.doc,” a subset of a much larger table with around 600 rows. If a user selects cells in at least two rows, the code then moves the unmarked cells to the bottom of the table where it hides them.
The data document contains a table with the three left columns visible, and the remaining columns are invisible. (Those hidden columns contain information for sorting the data alphabetically, in sequence order, and so forth.) We use the last column for sorting the table in the attached code.
It may well turn out that Graham Skan’s solution is a better course of action. He suggested that instead of moving and hiding the unwanted rows, one can just expand the user’s selected cells to include entire rows, and then one can copy those rows into another document for the user. The advantage here is that one does not then have to “clean up” the original document to restore it to its original state.
I intend to implement both systems in my program and then let the user choose whether to create a new document à la mode de Graham or use the current document with rows hidden à la mode de Hirstmg. I am therefore increasing the value of this question from 250 to 500 so that I can assign 250 points to the author of each solution.


Author Comment

ID: 35059189
Two comments above contain the code I used with the data below. My thoughts are in the previous comment.
      Points awarded as described above.
      Many thanks!

Author Closing Comment

ID: 35059205
My comments are above. Both solutions helped with the problem.

Both deserve credit

Author Comment

ID: 37054292
I apologize for opening this question again. If anyone is interested, there is a much simpler solution that I am currently using to fill adjacent cells in a table column. The following code puts a string (here “i”) in a column (here column 6 of the first table in the document) in cells from the integer x to the integer y. The code assumes that i, x, and y have been declared as integers:

Dim Sortcol As Column
Set Sortcol = ActiveDocument.Tables(1).Columns(6)
For i = x To y
    Sortcol.Cells(i).Range.Text = "i"
Next i

LVL 76

Expert Comment

ID: 37054434
Thanks JRA. It looks as if we were over-interpreting the question.

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Nice table. Huge mess. Maybe this was something you created way back before you figured out tabs or a document you received from someone else. Either way, using the spacebar to separate the columns resulted in a mess. Trying to convert text to t…
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
This video teaches the viewer how to align pictures around text while keeping the text properly aligned in the document.
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question