conditionally delete rows

Dear Experts.

I got one (1) table located in the active Document.

I wonder whether the following is possible using VBA

If cells with the specific string 'Attention' are found, the whole row is to be deleted.

Help is much appreciated. Thank you very much in advance.

Regards, Andreas
Andreas HermleTeam leaderAsked:
Who is Participating?
 
PandaPantsCommented:
This fixes the efficiency issue. (The previous code was adapted from a different solution.)
Sub RowKiller()

Dim sourceTable As Table
Dim cellContents As String, sSearchTerm As String
Dim iTotalRows As Integer, iTotalCols As Integer
Dim rIndex As Integer, cIndex As Integer

sSearchTerm = "Attention"
  
    For Each sourceTable In ActiveDocument.Tables
        ' Initialize row and column counters:
        iTotalRows = sourceTable.Rows.Count
        iTotalCols = sourceTable.Columns.Count
        ' Check for sSearchTerm starting from last row and moving back to row 1:
        For rIndex = iTotalRows To 1 Step -1
            For cIndex = 1 To iTotalCols
                sourceTable.Cell(rIndex, cIndex).Select
                If InStr(1, Selection.Text, sSearchTerm) > 0 Then
                    sourceTable.Rows(rIndex).Delete
                    Exit For ' cIndex = 1 To iTotalCols
                End If '
            Next cIndex  ' cIndex = 1 To iTotalCols
        Next rIndex ' = iTotalRows To 1 Step -1
    Next ' sourceTable In ActiveDocument.Tables
End Sub

Open in new window

0
 
PandaPantsCommented:
Try this. It's not super-efficient (because it starts again from the last row of the table after each deletion, so it searches the same rows multiple times), but it does the trick.
Sub RowKiller()

Dim sourceTable As Table
Dim cellContents As String, sSearchTerm As String
Dim iTotalRows As Integer, iTotalCols As Integer
Dim rIndex As Integer, cIndex As Integer

sSearchTerm = "Attention"
  
    For Each sourceTable In ActiveDocument.Tables
StartSearchFromTop:
        ' Initialize boolean and row counter:
        iTotalRows = sourceTable.Rows.Count
        iTotalCols = sourceTable.Columns.Count
        ' Check for sSearchTerm starting from last row and moving back to row 1:
        For rIndex = iTotalRows To 1 Step -1
            For cIndex = 1 To iTotalCols
                sourceTable.Cell(rIndex, cIndex).Select
                If InStr(1, Selection.Text, sSearchTerm) > 0 Then
                    sourceTable.Rows(rIndex).Delete
                    GoTo StartSearchFromTop
                End If '
            Next cIndex
        Next rIndex
    Next ' sourceTable In doc1.Tables
End Sub

Open in new window

0
 
Andreas HermleTeam leaderAuthor Commented:
Hi PandaPants,

great job, this did the trick! Thank you very much for your professional help. Regards, Andreas
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.