Autofill in Table

[Rishek]

I'm looking for a macro to fill in the blank cells in the 4th column of a table with the last value above. And only in non-empty rows.

A	2	1	Apple
A	3	1
3	3	3	Banana
3	4	5
	3
3	3		Cherry

Would become:

A	2	1	Apple
A	3	1	Apple
3	3	3	Banana
			Banana
3	4	5	Banana
	3		Banana
3	3		Cherry
			Cherry

Ideas?

[gmaxey]

Sub ScratchMacro()
'A basic Word macro coded by Greg Maxey
Dim lngIndex As Long
Dim oTbl As Table
Dim oRow As Row
Dim oRng As Range
  Set oTbl = Selection.Tables(1)
  For lngIndex = 2 To oTbl.Rows.Count
    Set oRow = oTbl.Rows(lngIndex)
    If Not Len(oRow.Range.Text) = 10 Then
      If Len(oRow.Cells(4).Range.Text) = 2 Then
        Set oRng = oRow.Previous.Cells(4).Range
        oRng.End = oRng.End - 1
        oRow.Cells(4).Range.Text = oRng.Text
      End If
    End If
  Next
lbl_Exit:
  Exit Sub
End Sub

[Rishek]

Did not quite work as intended. Got an error that tracked to the If Len command. Probably best I include a sample document. I've highlighted in red the rows where I would hope to see an added item. Clearly, I forgot to mention that I had rows with merged cells that could be skipped. Thanks for taking a look.

Table to Fill Column 4.docx

Also, what exactly do the numerical values after the if Len and If Not Len statements relate to (2 and 10 respectively)? I'm assuming whether the cells are empty or not, but I don't understand how that works.

[gmayor]

The following should work with your example
The len = 10 statement (or in the following case len > 10 indicates an empty row. Empty rows in your table have a length of 10 (there are also some merged rows which are shorter)
The len = 2 in Greg's macro is the length of an empty cell.

Sub Macro1()
Dim oTable As Table
Dim oRow As Row
Dim oRng As Range
Dim oCell As Range
    On Error Resume Next
    Set oTable = Selection.Tables(1)
    For Each oRow In oTable.Rows
        If Len(oRow.Range) > 10 Then 'The row is not empty
            Set oCell = oRow.Cells(4).Range
            oCell.End = oCell.End - 1 'Remove the cell end character from the range
            If Len(oCell) > 0 Then 'If the cell is not empty
                Set oRng = oRow.Cells(4).Range 'Grab the content of the cell
                oRng.End = oRng.End - 1
            Else 'The cell is empty so add the formatted text from the last cell that wasn't empty.
            'This should only cause a problem if the first row is not empty but has no value in column 4
                oCell.FormattedText = oRng.FormattedText
            End If
        End If
    Next oRow
lbl_Exit:
    Set oTable = Nothing
    Set oRow = Nothing
    Set oCell = Nothing
    Set oRng = Nothing
    Exit Sub
End Sub

[Rishek]

Gracias to both of you and thanks for the explanation.

[gmaxey]

You're welcome. Following up on the numbers 10 and 2 that I used, if you show non-printing characters in a document containing a table, you will see that each cell is terminated with a character that looks like a circle with four small tick marks one at 2, 4, 8 and 10 and each row is terminated using the same character. These are the end of cell/end row marks. They appear as a single character but have a length or two because they are made up by a combination of ASCII characters 13 and 7

So an empty row in a four column table has a text length = 10 and an empty cell in any table has a text length = 2

You can see the workings of a end of cell/row mark by stepping through the following code with an empty table:

Sub ScratchMacro()
'A basic Word macro coded by Greg Maxey
Dim oChr As Range
  For Each oChr In ActiveDocument.Range.Characters
    If Len(oChr) = 2 Then
      oChr.Select
      Debug.Print AscW(Mid(oChr, 1, 1))
      Debug.Print AscW(Mid(oChr, 2, 1))
    End If
  Next
lbl_Exit:
  Exit Sub
End Sub