none
VBA Table with unknown number of rows RRS feed

  • Question

  • Hi,

    I have got a word document with tables in it. This is going to be sent to users to fill in the cells in the table.

    In one of the tables the user can enter multiple rows into the table depending on how much information they want to add. 

    I would like to first get this information into a message box and then I will go from there.

    The first 2 columns are headings so don't need to be captured. there are 7 columns which need to be captured so from row 3 down depending on how many rows there are. I can do this using with statements and a loop (Next). The only problem is that the last row has been merged into one cell for the user to enter any other comments.

    So basically, can you do a loop that doesn't record the bottom row, so that I could do this separate. Or alternatively could I make it so that it records all of the rows but make it so that it knows that the bottom row only has 1 cell not 7?

    I am new to VBA but I do know the basic concepts.

    Thanks

    Ben

    Wednesday, October 23, 2013 9:33 AM

Answers

  • Hi Ben,

    Re 1 & 2. Instead of:
    Dim r, cell1, cell2, cell3, cell4, cell5, cell6, cell7, cell8
    you should use:
    Dim r As Long, Rng as Range
    Dim cell1 As String, cell2 As String, cell3 As String, cell4 As String
    Dim cell5 As String, cell6 As String, cell7 As String, cell8 As String

    Re 3. You can see what I mean by changing:
    cell1 = .Cells(1).Range.Text
    to:
    cell1 = cell1 & vbTab & .Cells(1).Range.Text
    This will preserve the contents of cell1 as the code iterates each loop.

    I'd have thought the rest was self-explanatory.

    As for whether the new code is any better, that depends on what you're trying to do - which you haven't clarified.


    Cheers
    Paul Edstein
    [MS MVP - Word]

    Monday, October 28, 2013 10:30 AM
  • Hi Ben,

    Capturing the cell contents isn't difficult. What will possibly cause much more of an issue for you are: (a) displaying the data in a tabular layout; and (b) the limited character count a message box can display.

    The following should get you started. As coded, it works on the first table in the document in the manner described in your post:

    Sub Demo()
    Dim i As Long, j As Long, Rng As Range, StrTxt As String
    With ActiveDocument.Tables(1)
      For i = 1 To .Rows.Count - 1
        With .Rows(i)
          For j = 3 To .Cells.Count
            Set Rng = .Cells(j).Range
            With Rng
              .End = .End - 1
              StrTxt = StrTxt & "|" & .Text
            End With
          Next
        End With
        StrTxt = StrTxt & "|"
      Next
      With .Rows(.Rows.Count)
        Set Rng = .Cells(3).Range
          With Rng
            .End = .End - 1
            StrTxt = StrTxt & "|" & .Text
          End With
      End With
      StrTxt = Right(StrTxt, Len(StrTxt) - 1)
      StrTxt = Replace(Replace(StrTxt, "||", vbCr), "|", vbTab)
      MsgBox StrTxt
    End With
    End Sub

    Note that, if any of your cells contain tabs and/or '|' characters, the output's alignment will be messed up. Even then, some misalignment is likely if the text length in the cells differs much.


    Cheers
    Paul Edstein
    [MS MVP - Word]

    Wednesday, October 23, 2013 11:37 AM

All replies

  • Hi Ben,

    Capturing the cell contents isn't difficult. What will possibly cause much more of an issue for you are: (a) displaying the data in a tabular layout; and (b) the limited character count a message box can display.

    The following should get you started. As coded, it works on the first table in the document in the manner described in your post:

    Sub Demo()
    Dim i As Long, j As Long, Rng As Range, StrTxt As String
    With ActiveDocument.Tables(1)
      For i = 1 To .Rows.Count - 1
        With .Rows(i)
          For j = 3 To .Cells.Count
            Set Rng = .Cells(j).Range
            With Rng
              .End = .End - 1
              StrTxt = StrTxt & "|" & .Text
            End With
          Next
        End With
        StrTxt = StrTxt & "|"
      Next
      With .Rows(.Rows.Count)
        Set Rng = .Cells(3).Range
          With Rng
            .End = .End - 1
            StrTxt = StrTxt & "|" & .Text
          End With
      End With
      StrTxt = Right(StrTxt, Len(StrTxt) - 1)
      StrTxt = Replace(Replace(StrTxt, "||", vbCr), "|", vbTab)
      MsgBox StrTxt
    End With
    End Sub

    Note that, if any of your cells contain tabs and/or '|' characters, the output's alignment will be messed up. Even then, some misalignment is likely if the text length in the cells differs much.


    Cheers
    Paul Edstein
    [MS MVP - Word]

    Wednesday, October 23, 2013 11:37 AM
  • Hi Paul,

    That's brilliant.

    I have now been able to get all of the information apart from the last row.

    How can I then capture the last row from a table.

    I would like a simple with statement if possible

    Thanks

    Ben



    • Edited by Ben1996 Wednesday, October 23, 2013 12:29 PM
    Wednesday, October 23, 2013 12:27 PM
  • The code I posted already extracts the last row which, per your description, I took to have two header columns before the 3rd, merged column. If it's all one column, change '.Cells(3).Range' to '.Cells(1).Range'.

    Cheers
    Paul Edstein
    [MS MVP - Word]

    Wednesday, October 23, 2013 8:49 PM
  • Hi Paul

    This is what I entered. the dim's called cell1 up to cell7 are repeated from rows 3 and below apart from the last row. On the last row there is just one cell which has the dim cell8. The code below doesn't work. Please could you tell me what i am doing wrong?

            


    Sub section4()

    On Error Resume Next

        Dim r, cell1, cell2, cell3, cell4, cell5, cell6, cell7, cell8

        For r = 3 To ActiveDocument.Tables(1).Rows.Count - 1

        With ActiveDocument.Tables(1).Rows(r)
          Cell1 = .Cells(1).Range.Text
          Cell2 = .Cells(2).Range.Text
          Cell3 = .Cells(3).Range.Text
          Cell4 = .Cells(4).Range.Text
          Cell5 = .Cells(5).Range.Text
          Cell6 = .Cells(6).Range.Text
          Cell7 = .Cells(7).Range.Text
          With ActiveDocument.Tables(6).Rows(.Rows.Count)
          With rng
            .End = .End - 1
           Cell8 = 
          End With
            MsgBox cell1 & " " & cell2 & " " & cell3 & " " & cell4 & " " & cell5 & " " & cell6 & " " & cell7
    msgbox cell8
          End With

    Next
    End Sub

    Thanks

    Ben

    Thursday, October 24, 2013 8:47 AM
  • Your code has a number of issues that need addressing, including:

    1. All defined variables are variants, rather than being long, string or range, as applicable;
    2. The Rng variable is undefined;
    3. Your Cell# variables get overwritten, rather than updated as each row is processed;
    4. Your Cell# variables get populated with the end-of-cell markers, not just the cell contents;
    5. The 'Cell8 =' expression is incomplete; and
    6. You're trying to retrieve whatever you want for Cell8 from a different table to the rest of the data.

    I note also that your code processes all columns in rows 3 to the penultimate row, whereas your initial post said you wanted the data from columns 3 to 7 in all rows except the last.


    Cheers
    Paul Edstein
    [MS MVP - Word]

    Thursday, October 24, 2013 10:58 AM
  • Hi Paul,

    Thank you for your help.

    If you notice it says 'For r = 3 To ActiveDocument.Tables(1).Rows.Count - 1' this counts all rows except the last row. I have now managed to solve my own problem as shown below.

    Sub section4()

    On Error Resume Next

        Dim r, cell1, cell2, cell3, cell4, cell5, cell6, cell7, cell8

        For r = 3 To ActiveDocument.Tables(1).Rows.Count - 1

        With ActiveDocument.Tables(1).Rows(r)
          Cell1 = .Cells(1).Range.Text
          Cell2 = .Cells(2).Range.Text
          Cell3 = .Cells(3).Range.Text
          Cell4 = .Cells(4).Range.Text
          Cell5 = .Cells(5).Range.Text
          Cell6 = .Cells(6).Range.Text
          Cell7 = .Cells(7).Range.Text
        end with
        next
           Cell8 = ActiveDocument.Tables(1).Rows(r).Cells(1).Range.Text

    With

    Next
    End Sub

     I know my coding needs a lot of work. as I said above I hadnt worked with VBA until monday so i am quite impressed. I dont really understand your comments below, please could you explain? 

    1. All defined variables are variants, rather than being long, string or range, as applicable;
    2. The Rng variable is undefined;
    3. Your Cell# variables get overwritten, rather than updated as each row is processed;
    4. Your Cell# variables get populated with the end-of-cell markers, not just the cell contents;
    5. The 'Cell8 =' expression is incomplete; and
    6. You're trying to retrieve whatever you want for Cell8 from a different table to the rest of the data.

    Is the code above any better, does it solve any of the issues you raised

    Thanks

    Thursday, October 24, 2013 12:53 PM
  • Hi Ben,

    Re 1 & 2. Instead of:
    Dim r, cell1, cell2, cell3, cell4, cell5, cell6, cell7, cell8
    you should use:
    Dim r As Long, Rng as Range
    Dim cell1 As String, cell2 As String, cell3 As String, cell4 As String
    Dim cell5 As String, cell6 As String, cell7 As String, cell8 As String

    Re 3. You can see what I mean by changing:
    cell1 = .Cells(1).Range.Text
    to:
    cell1 = cell1 & vbTab & .Cells(1).Range.Text
    This will preserve the contents of cell1 as the code iterates each loop.

    I'd have thought the rest was self-explanatory.

    As for whether the new code is any better, that depends on what you're trying to do - which you haven't clarified.


    Cheers
    Paul Edstein
    [MS MVP - Word]

    Monday, October 28, 2013 10:30 AM