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.

19593

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