Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 283
  • Last Modified:

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
0
AndreasHermle
Asked:
AndreasHermle
  • 2
1 Solution
 
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
 
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
 
AndreasHermleAuthor Commented:
Hi PandaPants,

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

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now