vba Deleting specific rows and columns in a word table

Question

I need to parse most of a word doc tables and delete the cells containing the string "Deleted". The tables have different formats so i need to change the start index depending on the format.i wrote a code where i start first deleting rows with all cells containing the "Deleted" string. The same strategy is used to delete the columns. It worked for the rows but not for the columns delete. When running through the table cells the column index is bigger than the actual available columns due to deleted columns.i dont know why it occured in the column delete part and not in the row delete one. The code below works for some of the tables but not all of them.

Dim msWord                          As Word.Application
Dim myDoc                           As Word.Document
Dim wordTable                       As Table
Dim r                               As Long
Dim c                               As Long
Dim col_del_cnt                     As Long
Dim row_del_cnt                     As Long
Dim i                               As Long
Dim col_index                       As Long
Dim param                           As Long
Dim Tbl                             As Table
Dim Tmpfile                         As String

Tmpfile = ThisWorkbook.Path & "\Template.docx"
    With msWord
      .Visible = True
      Set myDoc = .Documents.Open(Filename:=Tmpfile)
    End With

    With myDoc
      For i = 7 To 21
      Set wordTable = .Tables(i)
      If wordTable.Columns.Count < 2 Then
        col_index = 1
        param = wordTable.Columns.Count
      Else
        col_index = 2
        param = wordTable.Columns.Count - 1
      End If

      For r = 2 To wordTable.Rows.Count
        col_del_cnt = 0
        For c = col_index To wordTable.Columns.Count
          If InStr(1, wordTable.Cell(r, c).Range.Text, "Deleted", 1) Then
            col_del_cnt = col_del_cnt + 1
          End If
        Next c

        If col_del_cnt = param Then
          If r > wordTable.Rows.Count Then
            wordTable.Rows(wordTable.Rows.Count).Delete
          Else
            wordTable.Rows(r).Delete
          End If
            End If
      Next r
    Next
  End With

  With myDoc
       For i = 7 To 21
    Set wordTable = .Tables(i)
    If wordTable.Columns.Count < 2 Then
      col_index = 1
    Else
      col_index = 2
    End If

    For c = col_index To wordTable.Columns.Count
      row_del_cnt = 0
      For r = 2 To wordTable.Rows.Count
                  If InStr(1, wordTable.Cell(r, c).Range.Text, "Deleted", 1) Then '\Error located here'
        row_del_cnt = row_del_cnt + 1
          End If
      Next r

      If row_del_cnt = wordTable.Rows.Count - 1 Then
        If c > wordTable.Columns.Count Then
        wordTable.Columns(wordTable.Columns.Count).Delete
        Else
        wordTable.Columns(c).Delete
        End If
      End If
            Next c
          Next
      End With

I hope someone could help me to find the solution.

macropod macropod · Accepted Answer · 2018-09-20T03:31:26

It appears you're trying to delete both the row and the column when a cell has 'Deleted' in it. Obviously, if you use one loop to delete a row that has 'Deleted' in it, then a second loop to delete a column that has 'Deleted' in it, the second loop won't find anything. Try something based on:

Dim t As Long, r As Long, c As Long, ArrCols() As String
With myDoc
  For t = 21 To 7 Step -1
    With .Tables(t)
      If InStr(1, .Range.Text, "Deleted", 1) Then
        ReDim ArrCols(.Columns.Count)
        For r = .Rows.Count To 1 Step -1
          With .Rows(r)
            If InStr(1, .Range.Text, "Deleted", 1) Then
              For c = 1 To .Cells.Count
                If InStr(1, .Cells(c).Range.Text, "Deleted", 1) Then
                  ArrCols(c) = c
                End If
              Next
              .Delete
            End If
          End With
        Next r
        For c = UBound(ArrCols) To 1 Step -1
          If ArrCols(c) <> "" Then .Columns(c).Delete
        Next
      End If
    End With
  Next
End With

Note how all the loops involving deletions run backwards.

The fact your own code didn't throw errors with the row deletions was just a coincidence.

vba Deleting specific rows and columns in a word table

2 Answers