Keeping just the rows with specific word (BUT KEEP THE TITLE)

Kopko · #1 05-25-2017, 08:44 AM

Hello,

I have a lot of tables containing data in a pattern like this:
- A table that is the title of a company
- Below that, a table containing the data of this company
(I'm attatching an image showing this)

I have to keep just the rows that contains a specific word (in this example is "10/05/2017") and ALSO the table above (which is the title).

My idea was to paint in red the entire row that contains the specified word and then delete everything that is not painted in red. After that, I was planning to exclude eveything that is not painted in red. The problem is when I do that I'm not painting the title.

I tried this in many ways, I can't figure how to do that. This is what I struggling for now:

Quote:

Sub DeleteRowWithSpecifiedText()
Dim sText As String

sText = InputBox("Insert text to paint")
Selection.Find.ClearFormatting
With Selection.Find
.Text = sText
.Wrap = wdFindContinue
End With
Do While Selection.Find.Execute
If Selection.Information(wdWithInTable) Then
Selection.Rows.Shading.ForegroundPatternColor = wdColorRed
End If
Loop
End Sub

But this fail to:
Paint the entire row
Do not paint the title of the companys
I don't know how to exclude the rest of the rows.

If anyone can help me, please tell me a way to do that. I can't figure how.

Guessed · #2 05-25-2017, 04:01 PM

Can you post a sample document?

Do you want to retain the first row in each of the tables? Do you want to get rid of the company name table if the following table contains no hits?

Kopko · #3 05-25-2017, 07:45 PM

Quote:

Originally Posted by Guessed

Can you post a sample document?

Do you want to retain the first row in each of the tables? Do you want to get rid of the company name table if the following table contains no hits?

I'm uploading one sample, the language is in portuguese but I will try to explain for you:

The first table will always be a company name (just one row and one column). The next table above this one will be a table containing the word that I want to keep.

If the table below the title contains the word that I need (in any row), I want to delete everything in this table beside the rows that hits it.

Else if the table below the title doens't hit the specific word, I want to delete the entire table and ALSO the title table.

My english is a little bit rusty, I'm sorry. Did you get it?

Thanks a lot for your attention.

Guessed · #4 05-25-2017, 09:11 PM

Rather than deleting the rows, I would recommend you hide them from view. The following code does that in the document you posted.

Code:

Sub HideUnwantedRows()
  Dim sText As String, aTbl As Table
  Dim iRow As Integer, aRng As Range
  
  sText = InputBox("What text are you searching for?")
  For Each aTbl In ActiveDocument.Tables
    aTbl.Range.Select
    If aTbl.Range.Paragraphs(1).Style = "Agente" Then
      Set aRng = aTbl.Range
    ElseIf InStr(aTbl.Range.Text, sText) > 0 Then 'if there is at least one row
      For iRow = aTbl.Rows.Count To 1 Step -1
        If Not InStr(aTbl.Rows(iRow).Range.Text, sText) > 0 Then  'not this row
          aTbl.Rows(iRow).Range.Font.Hidden = True
        End If
      Next iRow
    Else
      If Not aRng Is Nothing Then 'hide both tables
        aRng.End = aTbl.Range.End
        aRng.Font.Hidden = True
        Set aRng = Nothing
      Else    'table is standalone
        aTbl.Range.Font.Hidden = True
      End If
    End If
  Next aTbl
  With ActiveWindow.View
    .ShowAll = False
    .ShowHiddenText = False
  End With
End Sub

Because the content is only hidden, it can be restored to view by doing a Select All and then going into the Font dialog ant turning off the Hidden attribute.

Kopko · #5 05-26-2017, 07:59 AM

Quote:

Originally Posted by Guessed

Because the content is only hidden, it can be restored to view by doing a Select All and then going into the Font dialog ant turning off the Hidden attribute.

Man, this solution is f****ng awesome! Thanks!!
I will take advantage of your good will... I have another task to do in this same document:

If you see the tables with data, the last column will always be "Serv/Rec." and below that a link to a legend like "Ser02".

If you go to the last table, there will the definition of this expression. I also have to keep this definition and hide (or delete) the rest that is not used. Is it still possible?

If this is asking too much, I already thank you for life! This was really awesome. I'm studying to do this by myself. Thanks a lot for your code!

Guessed · #6 05-27-2017, 02:32 AM

Hmm, that is a bit more complicated. I'll have a play with it and see if I can do a reasonably clear way of doing it. There are going to be plenty of assumptions based on the sample document you supplied. Are the documents always consistent with this layout?

Guessed · #7 05-27-2017, 04:28 AM

Try this version. If it works then we can look at speeding it up

Code:

Sub HideUnwantedRows2()
  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
  
  ActiveDocument.Range.Font.Hidden = False
  sText = InputBox("What text are you searching for?")
  
  For Each aTbl In ActiveDocument.Tables
    If aTbl.Range.Paragraphs(1).Style = "Agente" Then
      Set aRng = aTbl.Range
    ElseIf InStr(aTbl.Range.Text, sText) > 0 Then 'if there is at least one row
      For Each aRow In aTbl.Rows
        If Not InStr(aRow.Range.Text, sText) > 0 Then
          aRow.Range.Font.Hidden = True
        Else
          Set aCell = aRow.Cells(aRow.Cells.Count)
          sRefs = sRefs & "|" & Split(aCell.Range.Text, vbCr)(0)   'builds a list of all the wanted refs
        End If
      Next aRow
      Set aRng = Nothing
    Else
      If Not aRng Is Nothing Then 'hide both tables
        aRng.End = aTbl.Range.End
        aRng.Font.Hidden = True
        Set aRng = Nothing
      Else    'last table is standalone
        'Debug.Print sRefs
        For Each aRow In aTbl.Rows
          sTag = Split(aRow.Cells(1).Range.Text, vbCr)(0)
          'Debug.Print sTag
          aRow.Range.Font.Hidden = Not InStr(sRefs, sTag) > 0
        Next aRow
      End If
    End If
  Next aTbl
  With ActiveWindow.View
    .ShowAll = False
    .ShowHiddenText = False
  End With
End Sub

Kopko · #8 05-27-2017, 12:26 PM

Quote:

Originally Posted by Guessed

Try this version. If it works then we can look at speeding it up

Unfortunately, this version is not working.
Answering your question: yes, the documents are always consistent with this layout.

I noticed in this last code that the vector sRef is doing the job that is saving all the strings with the hyperlink.

But this line is never being executed:

Quote:

For Each aRow In aTbl.Rows
sTag = Split(aRow.Cells(1).Range.Text, vbCr)(0)
'Debug.Print sTag
aRow.Range.Font.Hidden = Not InStr(sRefs, sTag) > 0
Next aRow

I tried to move it to the final of this code but I'm not having success. Always get an error.

Again, thanks a lot for your help man! Someday I will be like you and help people with codes like you are doing!

Guessed · #9 05-27-2017, 10:09 PM

It seems to work when stepping through but not running at speed.

Try this version which specifies the last table explicitly

Code:

Sub HideUnwantedRows3()
  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
  
  ActiveDocument.Range.Font.Hidden = False
  sText = InputBox("What text are you searching for?")
  
  For Each aTbl In ActiveDocument.Tables
    If aTbl.Range.Paragraphs(1).Style = "Agente" Then
      Set aRng = aTbl.Range
    ElseIf InStr(aTbl.Range.Text, sText) > 0 Then 'if there is at least one row
      For Each aRow In aTbl.Rows
        If Not InStr(aRow.Range.Text, sText) > 0 Then
          aRow.Range.Font.Hidden = True
        Else
          Set aCell = aRow.Cells(aRow.Cells.Count)
          sRefs = sRefs & "|" & Split(aCell.Range.Text, vbCr)(0)   'builds a list of all the wanted refs
        End If
      Next aRow
      Set aRng = Nothing
    Else
      If Not aRng Is Nothing Then 'hide both tables
        aRng.End = aTbl.Range.End
        aRng.Font.Hidden = True
        Set aRng = Nothing
      End If
    End If
  Next aTbl
  With ActiveDocument.Tables(ActiveDocument.Tables.Count)
    Debug.Print sRefs
    For Each aRow In .Rows
      sTag = Split(aRow.Cells(1).Range.Text, vbCr)(0)
      'Debug.Print sTag
      aRow.Range.Font.Hidden = Not InStr(sRefs, sTag) > 0
    Next aRow
  End With
  
  With ActiveWindow.View
    .ShowAll = False
    .ShowHiddenText = False
  End With
End Sub

Kopko · #10 05-28-2017, 03:20 AM

Quote:

Originally Posted by Guessed

It seems to work when stepping through but not running at speed.

Now the last table is at full view, is not hiding any of the sTags.

Again, I really grateful for your help!

Kopko · #11 05-28-2017, 10:26 AM

(Sorry for double posting, my bad).
I just noticed why this was not working in the original document:
In the beggining, the word that I want to search is "08/05/2017"
When it comes to the last table, if there is this date written anywhere inside the table, this line will be executed:

Quote:

For Each aRow In aTbl.Rows
If Not InStr(aRow.Range.Text, sText) > 0 Then
aRow.Range.Font.Hidden = True
Else
Set aCell = aRow.Cells(aRow.Cells.Count)
sRefs = sRefs & "|" & Split(aCell.Range.Text, vbCr)(0) 'builds a list of all the wanted refs
End If
Next aRow

So when it goes to the last table, every line is hidden (beside the ones with the "08/05/2017")

I'm trying to fix that. Maybe we can do something to check always if the text "08/05/2017" is in the second column (aRow, 2) of each table. That is because the important rows to keep in this table is just if the date (the word that I'm searching) inside the second column named as "Inicio Previsto" (that means Expected Start") matchs.

EDIT:
I just got it done adding this line to show the last table again:

Quote:

With ActiveDocument.Tables(ActiveDocument.Tables.Count) 'Beside this With I added the line below:
ActiveDocument.Range.Font.Hidden = False

But now my code is a little bit redundant. When it goes to the last table, first it hides everything, then it shows everything again and finally starts to hide the rows that will not be used. Can we do this in a better way?

Guessed · #12 05-28-2017, 05:59 PM

I've made lots of assumptions based on the content you provided in your sample document. If any of those assumptions are incorrect in your other docs then those need to be resolved. For instance, I assume that the last table in the document is where the references are sitting.

In my testing, the last table would have been ignored in the table loop (since it didn't trigger any of the If tests) and then the following code should address it directly

Code:

  With ActiveDocument.Tables(ActiveDocument.Tables.Count)
    Debug.Print sRefs
    For Each aRow In .Rows
      sTag = Split(aRow.Cells(1).Range.Text, vbCr)(0)
      'Debug.Print sTag
      aRow.Range.Font.Hidden = Not InStr(sRefs, sTag) > 0
    Next aRow
  End With

Try stepping through this section of the code to see if you can work out why it isn't working in your document. Maybe add a couple of lines of code to reveal the entire table before looping through the rows eg.

Code:

  With ActiveDocument.Tables(ActiveDocument.Tables.Count)
    Debug.Print sRefs
    .Range.Font.Hidden = False
    .Select
    For Each aRow In .Rows
      sTag = Split(aRow.Cells(1).Range.Text, vbCr)(0)
      'Debug.Print sTag
      aRow.Range.Font.Hidden = Not InStr(sRefs, sTag) > 0
    Next aRow
  End With

Note: your code solution of adding ActiveDocument.Range.Font.Hidden = False will reveal the entire document, not just the last table

Kopko · #13 05-29-2017, 04:55 AM

Hey Buddy,

I made some changes to keep the first row of the table data (and also of the last one). ("If Not (InStr(aRow.Range.Text, sText) > 0 Or InStr(aRow.Range.Text, "Local - Equipamento") > 0) Then").
These first rows are the description from what comes above. I tested in several samples and I think it is running perfectly. You said before that when we have the final code maybe ou can change some things to make it faster (or lighter, because it seems to slow the computer when the code run)

So here is the final version that I'm using and it's working fine:

Code:

Sub HideUnwantedRows3()
  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
  
  ActiveDocument.Range.Font.Hidden = False
  sText = InputBox("What text are you searching for?")
  
  For Each aTbl In ActiveDocument.Tables
    If aTbl.Range.Paragraphs(1).Style = "Agente" Then
      Set aRng = aTbl.Range
    ElseIf InStr(aTbl.Range.Text, sText) > 0 Then 'if there is at least one row
      For Each aRow In aTbl.Rows
        If Not (InStr(aRow.Range.Text, sText) > 0 Or InStr(aRow.Range.Text, "Local - Equipamento") > 0) Then
          aRow.Range.Font.Hidden = True
        Else
          Set aCell = aRow.Cells(aRow.Cells.Count)
          sRefs = sRefs & "|" & Split(aCell.Range.Text, vbCr)(0)   'builds a list of all the wanted refs
        End If
      Next aRow
      Set aRng = Nothing
    Else
      If Not aRng Is Nothing Then 'hide both tables
        aRng.End = aTbl.Range.End
        aRng.Font.Hidden = True
        Set aRng = Nothing
      End If
    End If
  Next aTbl
  With ActiveDocument.Tables(ActiveDocument.Tables.Count)
    Debug.Print sRefs
    .Range.Font.Hidden = False
    .Select
    For Each aRow In .Rows
      sTag = Split(aRow.Cells(1).Range.Text, vbCr)(0)
      'Debug.Print sTag
      aRow.Range.Font.Hidden = Not (InStr(sRefs, sTag) > 0 or InStr(aRow.Range.Text, "Serviço / Recomendação") > 0)
    Next aRow
  End With
  
  With ActiveWindow.View
    .ShowAll = False
    .ShowHiddenText = False
  End With
End Sub

Again. I want to thank you for your help. It was an awesome experience! I already learned a lot.

Guessed · #14 05-29-2017, 07:03 PM

The majority of the code slowness is due to the screen updating and repagination as the code runs. We can turn that off while the code is running but I've taken the cheats way out and just moved the selection to the top of the document so all the changes take place off screen. I've also tightened the code to keep your heading rows and only look in the second column.

Code:

Sub HideUnwantedRows4()
  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("What text are you searching for?")
  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 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
        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 "There were no table rows with " & sText & " in Column 2", vbInformation + vbOKOnly, "Not found"
    ActiveDocument.Range.Font.Hidden = False    'show everything
  End If
End Sub

Kopko · #15 05-30-2017, 08:19 AM

Guessed, the code is working perfectly. I decided to do a few changes (please don't get mad at me).

1) I came back a little bit and decided to also keep the lines that contains the date (text typed) in the third column as well. I did this adding this condition inside this "If Not":

Code:

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

2) But now, I want to change the color (to red) of the rows that contains the word typed in the second column. I did like this:

Code:

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

This seems to work, but I also want in the last table, to paint the reference "sRef" (in red) of the line that I'm changing the color (just if the date typed matchs in the second column). This I'm not sure how to do.

Again! Amazing Work. Your solution to go to the top of the screen seems to solve the problem of slowness. I just wondering if painting the rows it's too much for the software to handle.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Open file which contains specific words in title	Nick70	PowerPoint	2	06-08-2016 06:55 AM
Formatting all tables in doc with specific word in title	jeffreybrown	Word VBA	2	05-01-2016 06:05 PM
Copying specific columns of a table to WORD and deleting rows	ffinley	Word VBA	5	12-07-2015 04:01 PM
Export calendar events from multiple calendars with specific title	rasmus	Outlook	0	02-06-2015 01:58 AM
Extracting specific rows	sbdk82	Excel	4	09-07-2014 10:24 PM