Microsoft Office Forums - View Single Post - Keeping just the rows with specific word (BUT KEEP THE TITLE)

Kopko · 05-30-2017, 09:58 PM

Quote:

Originally Posted by Guessed

Do you want the red if the sText appears in the second column but not if it appears in the third column?

Yes that's it. When the sText appears in the second column I want to paint this row in red and also the referece in the last table that matchs with this row (you called as sRef, the last table reference).

The red if the sText appers just in the second column (and not in the third) it's done. The code below is doing that and it's working great!

Code:

Sub PDIc()
  Dim sText As String, aTbl As Table, aHL As Hyperlink, aCell As Cell
  Dim iRow As Integer, aRng As Range, aRngTgt As Range, aRow As row
  Dim sRefs As String, aRowRng As Range, sTag As String, bHit As Boolean

  ActiveDocument.Range.Font.Hidden = False
  sText = InputBox("Digite a data a ser mantida")
  Selection.HomeKey Unit:=wdStory, Extend:=wdMove

  For Each aTbl In ActiveDocument.Tables
    If aTbl.Range.Paragraphs(1).Style = "Agente" Then
      Set aRng = aTbl.Range

    ElseIf aTbl.Cell(1, 1).Range.Text Like "Local*" Then
      bHit = False
      For iRow = 2 To aTbl.Rows.Count
        Set aRow = aTbl.Rows(iRow)
        If Not (InStr(aRow.Cells(2).Range.Text, sText) > 0 Or InStr(aRow.Cells(3).Range.Text, sText) > 0) Then
          aRow.Range.Font.Hidden = True
        Else
          bHit = True
          Set aCell = aRow.Cells(aRow.Cells.Count)
          sRefs = sRefs & "|" & Split(aCell.Range.Text, vbCr)(0)   'builds a list of all the wanted refs
            If (InStr(aRow.Cells(2).Range.Text, sText) > 0) Then
                 aRow.Range.Font.ColorIndex = wdRed
             End If
        End If
      Next iRow
      If Not bHit Then
        aRng.End = aTbl.Range.End
        aRng.Font.Hidden = True
      End If
      Set aRng = Nothing
    End If
  Next aTbl

  If Len(sRefs) > 0 Then
    With ActiveDocument.Tables(ActiveDocument.Tables.Count)
      Debug.Print sRefs
      .Range.Font.Hidden = False
      For iRow = 2 To .Rows.Count
        sTag = Split(.Rows(iRow).Cells(1).Range.Text, vbCr)(0)
        .Rows(iRow).Range.Font.Hidden = Not InStr(sRefs, sTag) > 0
      Next iRow
    End With

    With ActiveWindow.View
      .ShowAll = False
      .ShowHiddenText = False
    End With
  Else
    MsgBox "A data " & sText & " não possui início previsto em nenhum dos Agentes", vbInformation + vbOKOnly, "Não encontrado"
    ActiveDocument.Range.Font.Hidden = False    'show everything
  End If
End Sub

But I don't know how to change the color of the last table as well. Dictionary object is a viable solution? I will search about that.
One solution that I thought is to paint the remaining tables (after hiding) in red and then paint back to black the rows where columns matchs with sText.
The sample is always in black (everything). The first sample that I added here was already painted manually.

Thank you again sir!