# Solved: Show/Hide rows in a table on a Mail Merge document



## cherry_?! (Mar 11, 2005)

Hi, I am really hoping someone can help.

I am working on a MS Word mail merge document (whose format I do control). I need to populate a table on my document that lists the Fund #, Name and Value for up to 7 funds (3 columns and up to 7 rows). However, they do not want to have any empty rows in the table - if there are only 3 funds in the datasource, only 3 rows should show in the table. I thought there would be a way to use a bookmark to do this, but I haven't been able to figure it out.

Please help,


----------



## cristobal03 (Aug 5, 2005)

Hmm.

I don't use a lot of Mail Merge, but my instinct tells me you'll need to use VBA for this. Is this 7-column-maximum table the only table in the document? If not, is it the only table (or group of tables) that may or may not have empty rows?

I'm thinking you'd have to run a macro to check all the tables for empty rows, then delete those rows. That seems kind of drastic though.

Maybe someone who knows more than I will come along.

chris.


----------



## cherry_?! (Mar 11, 2005)

So you think that these rows will have to be a delete after the merge has been done, rather than functions run during the merge?


----------



## cherry_?! (Mar 11, 2005)

The mail merge is for 4500 records, so while there is only one table, after creating the merge that means it will have to scan 4500 tables 7 rows each. 

If I am understanding your suggestion.


----------



## cristobal03 (Aug 5, 2005)

What's the data source?

chris.


----------



## cherry_?! (Mar 11, 2005)

The datasource I will be receiving will be in excel - I could import it into Access and merge from there, however.


----------



## cristobal03 (Aug 5, 2005)

Well, if you prefer the mechanism of pulling from Word instead of pushing from somewhere else, I don't know how you'd control Word's tables without just running through the post-merge document and deleting empty rows.

You could probably push either from Excel or Access, but I wouldn't know how to do it without man-handling the formatting via a very large routine.

chris.


----------



## cristobal03 (Aug 5, 2005)

[bump]

If I had to estimate a benchmark for checking 4500 tables for empty rows and delete, say, half of them, I'd say maybe a minute to run. The deleting would be the long part. But it may be much shorter than that; I've never tried something quite like this--on so many tables, that is.

chris.


----------



## cherry_?! (Mar 11, 2005)

I have been working on the VBA for deleting the empty rows but I am having trouble determining (via the code) if the row is empty - none of them are being found empty. Any suggestions on the actual VBA code - I have tried ISEMPTY with a cell range reference, I have also tried using "" to compare to an empty cell, but neither comparison works.

Help.


----------



## cristobal03 (Aug 5, 2005)

Well, hmmm. Following is a lot of speculation.

Arguably the best way to check for an empty string in VBA is to use the following test:

Len(_string_ & vbNullString) > 0​If that expression returns *True*, the string is not empty (otherwise known as a *zero-length string*, or *ZLS*). So that'd probably be the test to use.

Now, for the speculation.

I'm not sure if there would ever be a ZLS in a Word selection that includes an end-of-paragraph--like the contents of a table cell, for example. I'm not sure if paragraph markers are hidden characters or if they act more like metadata in that they contain data about the document but aren't included in the document itself. So it may be the case that there will not be a case where you encouter a string the length of which is zero.

You might be able to chop or chomp the end of the string and test the length of that, _if_ the paragraph marker is included in the selection. You might be able to use *IsNull* (rather than *IsEmpty*) to successfully determine the contents of a cell. But if it's possible, you should use the *Len* technique. Could you post the code you've tried so far? That'll give me a better idea of what you've tried, and of the requirement in general.

chris.


----------



## cherry_?! (Mar 11, 2005)

Here is the preliminary code - right now I am just trying to loop through a document and find every third table (which works properly), then evaluate the first cell in each row of the table- visually I can see which rows are blank so I am just trying to get a message (at this point) to tell me which rows are meeting my evaluation criteria. Once the criteria is working correctly, I will add the code to delete the row. Right now I'm just stabbing in the dark to find the code that will work.

Sub CountTables()
Dim tableNum, tblcnt, rowNum, rowcnt As Integer
Dim message As String
Dim cellcontents As String

tableNum = ActiveDocument.Tables.Count
For tblcnt = 3 To tableNum Step 3
ActiveDocument.Tables(tblcnt).Select
rowNum = ActiveDocument.Tables(tblcnt).Rows.Count
For rowcnt = 1 To rowNum
cellcontents = ActiveDocument.Tables(tblcnt).Cell(rowcnt, 1).Range.Text
If IsNull(ActiveDocument.Tables(tblcnt).Cell(rowcnt, 1).Range.Text) Then
message = MsgBox("Row : " & rowcnt & "This is the formula the current cell " & cellcontents)
Else
message = MsgBox("Not Null")
End If
Next rowcnt
message = MsgBox("This is table number: " & tblcnt & " and it has " & rowNum & " rows.")
Next tblcnt
end sub


----------



## cherry_?! (Mar 11, 2005)

One of the things that I am finding is that there is a hidden value in each cell - a small square. I think that might be the issue but I am still trying to find where the square is coming from and how to ignore it.

As I step through the code even when there is text in the cell it ends with that small square.


----------



## cristobal03 (Aug 5, 2005)

Well, let's see. First, I believe all internal collections are zero-based. So, if you wanted to start with the third table and check every following third table, *tblcnt* would need to be 2 not 3.

Also, unlike some other languages, VBA does not allow you to type a set of variables with a single type declaration. Your line

Dim tableNum, tblcnt, rowNum, rowcnt As Integer​only establishes *rowcnt* as an Integer datum.

I'm going to slightly modify your code.


```
Sub CountTables()
  Dim intTblNum As Integer
  Dim intTblCnt As Integer
  Dim intRowNum As Integer
  Dim intRowCnt As Integer

  Dim strCellContents As String

  intTblNum = ActiveDocument.Tables.Count

  For intTblCnt = 2 To intTblNum Step 3
    With ActiveDocument.Tables(intTblCnt)
      intRowNum = .Rows.Count
      For intRowCnt = 1 To intRowNum
        strCellContents = .Cell(intRowCnt, 1).Range.Text
        If Len(strCellContents & vbNullString) > 0 Then
          MsgBox "The contents of the cell are:" & vbNewline & vbNewline & strCellContents
        Else
          MsgBox "Cell 1 of row " & intRowCnt & " in table " & intTblCnt & " is empty."
        End If
      Next intRowCnt
    End With
  Next intTblCnt
End Sub
```
Give that a try. Mind, it's airware.

chris.


----------



## cristobal03 (Aug 5, 2005)

[bump]

Yep, that small square is almost certainly a paragraph marker (or some other similar marker indicative of the table structure). Take the cell's content and put it into a string, then chop the last character of the string. If the *Len* of the resulting string is zero the cell is empty.

Add a line to the code:


```
strCellContents = .Cell(intRowCnt, 1).Range.Text
  strCellContents = Left(strCellContents, Len(strCellContents) - 1) ' added line
  If Len(strCellContents & vbNullString) > 0 Then
```
chris.


----------



## hmorales (Feb 6, 2006)

I think that from the data on an Access table, a query can be generated with a calculated field to handle those funds, so only they will appear whenever they have data.


----------



## cherry_?! (Mar 11, 2005)

SUCCESS!!

Although my loop for every third table was working nothing else was. I have added your suggestions and now it is finding the blank rows properly.

Thank you for your help! There turned out to be 2 of these marker characters, so with them removed it was able to correctly find the blank rows. 

Here is the final working code:
Sub CountTables()
Dim intTblNum As Integer
Dim intTblCnt As Integer
Dim intRowNum As Integer
Dim intRowCnt As Integer

Dim strCellContents As String

intTblNum = ActiveDocument.Tables.Count

For intTblCnt = 3 To intTblNum Step 3
With ActiveDocument.Tables(intTblCnt)
intRowNum = .Rows.Count
For intRowCnt = 1 To intRowNum
strCellContents = .Cell(intRowCnt, 1).Range.Text
strCellContents = Left(strCellContents, Len(strCellContents) - 2) ' added line
If Len(strCellContents & vbNullString) > 0 Then
MsgBox "The contents of the cell are:" & vbNewLine & vbNewLine & strCellContents
Else
MsgBox "Cell 1 of row " & intRowCnt & " in table " & intTblCnt & " is empty."
End If
Next intRowCnt
End With
Next intTblCnt
End Sub


----------



## cristobal03 (Aug 5, 2005)

:up: Glad I could help.

chris.


----------

