Deleting a row containing specific text in multiple tables in a Worddocument

[Rob]

Hi,

I have a Word document which has text, as well as many tables in it.

One cell of the tables may contain a text string (eg "foo bar"). I am
trying to write a macro which will delete the entire row in which this
occurs in the table.

Since I have hundreds of tables, I would like this to be automated. I
was doing a Ctrl-F & searching for the string, then if I found it I
was manually deleting the row. This way obviously sucks.

From searching on the site, I found a similar thread & tried copying
the code from it. The code I have tried is:

Public Sub Delete_Row()
Dim oTable As Table
Dim oRow As Row

With oTable
For Each oRow In .Rows
If oRow.Cells(1).Range.Text = "Foo Bar" Then
oRow.Delete
End If
Next oRow
End With

End Sub

***********************
I am getting the following error: Run-Time Error '91': Object
Variable or With Block Variable Not Set.

What is wrong here?

Thanks in advance,
Rob

 

[That Guy]

Public Sub Delete_Row()

Dim oTable As Table
Dim oRow As Row

With oTable
     For Each oRow In .Rows
          If oRow.Cells(1).Range.Text = "Foo Bar" Then
               oRow.Delete
          End If
     Next oRow
End With

End Sub

***********************
I am getting the following error:  Run-Time Error '91':  Object
Variable or With Block Variable Not Set.

Hi Rob,

the error you are getting stems from the use of an object that has not
been assigned a value correctly.

IN this instance it would appear first off that you are using the
object oTable before assigning it a value. You have mentioned that
your document has more than one table but it seems that you are only
trying to iterate through the rows of a single table.

I would suggest the following:

Public Sub Delete_Row()
Dim oTable As Table
Dim oRow As Row

Set oTable = ActiveDocument.Tables(1)

With oTable
For Each oRow In .Rows
If oRow.Cells(1).Range.Text = "Foo Bar" Then
oRow.Delete
End If
Next oRow
End With

End Sub

This will allow you to iterate through the rows in the first table.
The other thing that is wrong here is that you are trying to compare
strings using the equal sign. In VBA this is not the moist reliable
method for comparison, also in a table the text of a cell contains
more than what you see, it also contains a special character that word
recognizes as the end of cell so you need to include that in any text
search that you are doing. the easiest way is to use the Like operator
(http://msdn.microsoft.com/en-us/library/aa188509(office.10).aspx)
so it would look like this:

Public Sub Delete_Row()
Dim oTable As Table
Dim oRow As Row

Set oTable = ActiveDocument.Tables(1)

With oTable
For Each oRow In .Rows

If oRow.Cells(1).Range.Text Like "*Foo Bar*" Then

oRow.Delete
End If
Next oRow
End With

End Sub

Hope that helps,

Ian

 

[Rob]

Hi Rob,

the error you are getting stems from the use of an object that has not
been assigned a value correctly.

IN this instance it would appear first off that you are using the
object oTable before assigning it a value. You have mentioned that
your document has more than one table but it seems that you are only
trying to iterate through the rows of a single table.

I would suggest the following:


Set oTable = ActiveDocument.Tables(1)



This will allow you to iterate through the rows in the first table.
The other thing that is wrong here is that you are trying to compare
strings using the equal sign. In VBA this is not the moist reliable
method for comparison, also in a table the text of a cell contains
more than what you see, it also contains a special character that word
recognizes as the end of cell so you need to include that in any text
search that you are doing. the easiest way is to use the Like operator
(http://msdn.microsoft.com/en-us/library/aa188509(office.10).aspx)
so it would look like this:


Set oTable = ActiveDocument.Tables(1)


             If oRow.Cells(1).Range.Text Like "*Foo Bar*" Then



Hope that helps,

Ian- Hide quoted text -

- Show quoted text -

Ian,

Yes, that cleared it right up & did exactly what I wanted. You just
saved me HOURS of tedious manual deletions!!

And, I think I'm really starting to like VBA (had never used it
before, but a co-worker suggested to me that it would speed up these
repetive tasks). Now I just need to see what else I can use it on -
so I may be writing again with questions.

Thank you so much!!
Rob

 

[Greg Maxey]

I'll have to take your word for it, but I don't follow how Ian's code did
anything close to what you wanted. Your post states that you have hundreds
of tables and you want to automate the process. You also say that one cell
may contain the text string "foo bar." You don't say that text is in the
first cell nor do you say that it is the only text in the cell. Could the
cell contain additional text such as "This is foo bar" or "That is foo bar?"
Testing here oRow.Cells(1).Range.Text Like "Foo Bar" does not return true on
cells containing just "Foo Bar."

Again if Ian's code works for you good and well (and Ian I certainly do not
intend to criticize), but to process all tables you would need something
like a For ... Each statement and to process each cell you would need
another For ... Each statement. Something like this perhaps:

Sub ScratchMaco()
Dim oTbl As Word.Table
Dim oCell As Word.Cell
For Each oTbl In ActiveDocument.Tables
For Each oCell In oTbl.Range.Cells
If InStr(oCell.Range.Text, "foo bar") > 0 Then
oTbl.Rows(oCell.Range.Information(wdEndOfRangeRowNumber)).Delete
End If
Next oCell
Next oTbl
End Sub