Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 11-12-2020, 12:14 PM
JingleBelle JingleBelle is offline Search in Nested Tables to Delete Rows Windows 10 Search in Nested Tables to Delete Rows Office 2016
Novice
Search in Nested Tables to Delete Rows
 
Join Date: Nov 2020
Posts: 18
JingleBelle is on a distinguished road
Default Search in Nested Tables to Delete Rows

I often received large documents with many, many tables. Most of the tables contain another table (i.e., nested table). I need to search only the nested tables for rows containing certain text. If that text is found, I need to delete that row. Unfortunately, the text may also appear in the main table. I tried to write code (provided below) to do this; but it does not work. Given my VBA skillset, that is no surprise. I am hoping one of the experts, here, would take a look at it and point out the errors.



I have attached an example of the files that I receive. The main tables can be several rows and contain one or more nested tables plus text.

Code:
Sub SearchNestedTables()

'Search nested table rows for text and delete those rows
'My code does not work

Application.ScreenUpdating = False

    Dim MainTable As Table
    Dim NestedTable As Table
 
    For Each MainTable In ActiveDocument.Tables
        For Each NestedTable In MainTable.Tables
    With ActiveDocument.Range
        With .Find
            .ClearFormatting
            .Replacement.ClearFormatting
            .Text = "XXX text"
            .Replacement.Text = ""
            .Forward = True
            .Wrap = wdFindStop
            .Format = False
            .MatchWildcards = True
            .Execute
        End With
        Do
            If .Information(wdWithInTable) = True Then
                With NestedTable.Rows.Range
                .Rows(1).Delete
                End With
            End If
            .Collapse wdCollapseEnd
        Loop While .Find.Execute
    End With
        Next NestedTable
     Next MainTable 
Application.ScreenUpdating = True
MsgBox "Done!", vbInformation
End Sub
Attached Files
File Type: docx NestedTablesTest.docx (25.8 KB, 7 views)
Reply With Quote
  #2  
Old 11-12-2020, 12:33 PM
gmaxey gmaxey is offline Search in Nested Tables to Delete Rows Windows 10 Search in Nested Tables to Delete Rows Office 2016
Expert
 
Join Date: May 2010
Location: Brasstown, NC
Posts: 1,421
gmaxey is a jewel in the roughgmaxey is a jewel in the roughgmaxey is a jewel in the roughgmaxey is a jewel in the rough
Default

I found and adapted this which may work for you:

Code:
Sub QuerryAllTables()
Dim oTbl As Table
Dim oRng As Range
  For Each oTbl In fcnCollectDocTables
    'MsgBox "Cell count: " & oTbl.Range.Cells.Count & " - Nesting level:" & oTbl.NestingLevel
    If oTbl.NestingLevel > 1 Then
      Set oRng = oTbl.Range
      With oRng.Find
        .Text = "XXX text"
        Do While .Execute
         If oRng.InRange(oTbl.Range) Then
           oRng.Rows(1).Delete
         Else
           Exit Do
         End If
        Loop
      End With
    End If
  Next
lbl_Exit:
  Exit Sub
End Sub

Function fcnCollectDocTables(Optional ByVal oDoc As Document) As Collection
'Returns all tables (top level and nested) in one collection.
Dim colStack As New Collection
Dim oTbl As Table
  Set fcnCollectDocTables = New Collection
  If Documents.Count > 0 And oDoc Is Nothing Then
    Set oDoc = ActiveDocument
  Else
    GoTo lbl_Exit
  End If
  colStack.Add oDoc.Tables
  Do While colStack.Count > 0
    For Each oTbl In colStack(1)
      fcnCollectDocTables.Add oTbl
      If oTbl.Tables.Count > 0 Then colStack.Add oTbl.Tables
    Next
    colStack.Remove 1
  Loop
lbl_Exit:
  Exit Function
End Function
__________________
Greg Maxey
Please visit my web site at http://www.gregmaxey.com/
Reply With Quote
  #3  
Old 11-12-2020, 12:38 PM
JingleBelle JingleBelle is offline Search in Nested Tables to Delete Rows Windows 10 Search in Nested Tables to Delete Rows Office 2016
Novice
Search in Nested Tables to Delete Rows
 
Join Date: Nov 2020
Posts: 18
JingleBelle is on a distinguished road
Default

Thank you! I will give this a whirl. I appreciate the help (well, actually, the complete rewrite).
Reply With Quote
  #4  
Old 11-12-2020, 01:00 PM
JingleBelle JingleBelle is offline Search in Nested Tables to Delete Rows Windows 10 Search in Nested Tables to Delete Rows Office 2016
Novice
Search in Nested Tables to Delete Rows
 
Join Date: Nov 2020
Posts: 18
JingleBelle is on a distinguished road
Default

Hey, Greg.


Your code worked like a charm, of course. I had hoped my attempt at coding this would be close, but from the looks of yours, I was not even in the ballpark. LOL!


Thank you, again!
Reply With Quote
  #5  
Old 11-12-2020, 07:46 PM
Guessed's Avatar
Guessed Guessed is offline Search in Nested Tables to Delete Rows Windows 10 Search in Nested Tables to Delete Rows Office 2016
Expert
 
Join Date: Mar 2010
Location: Canberra/Melbourne Australia
Posts: 3,932
Guessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant future
Default

Greg

This is a very interesting example of a recursive function to return a collection. I'm intrigued that it works to build a collection which appears to be recreated from New with each recursion. I'm going to have to study this to work how you did that. I would have expected the collection to be created in the Sub and then passed in to the function for filling.

On a speed front, I would think that looping through all the tables appears to be less efficient than just searching for the text and then checking if it happens to be in a nested table. Would this code not do the same thing without the recursion?
Code:
Sub FlyTheNest()
  Dim aRng As Range
  Set aRng = ActiveDocument.Range
  With aRng.Find
    .ClearFormatting
    .Text = "XXX text"
    .Forward = True
    .Wrap = wdFindStop
    .Format = False
    While .Execute
      'aRng.Select    'testing purposes only
      If aRng.Information(wdWithInTable) Then
        If aRng.Tables(1).NestingLevel > 1 Then
          aRng.Rows(1).Delete
        End If
      End If
    Wend
  End With
End Sub
__________________
Andrew Lockton
Chrysalis Design, Melbourne Australia
Reply With Quote
  #6  
Old 11-13-2020, 04:47 AM
JingleBelle JingleBelle is offline Search in Nested Tables to Delete Rows Windows 10 Search in Nested Tables to Delete Rows Office 2016
Novice
Search in Nested Tables to Delete Rows
 
Join Date: Nov 2020
Posts: 18
JingleBelle is on a distinguished road
Default

Thank you! This code works, too. I get the same results with both. Awesome!
Reply With Quote
  #7  
Old 11-13-2020, 07:36 AM
gmaxey gmaxey is offline Search in Nested Tables to Delete Rows Windows 10 Search in Nested Tables to Delete Rows Office 2016
Expert
 
Join Date: May 2010
Location: Brasstown, NC
Posts: 1,421
gmaxey is a jewel in the roughgmaxey is a jewel in the roughgmaxey is a jewel in the roughgmaxey is a jewel in the rough
Default

Andrew,

It would of course. When I saw the OP, I just remembered a bit of code I have sacked away and pulled it up to modify and test.
__________________
Greg Maxey
Please visit my web site at http://www.gregmaxey.com/
Reply With Quote
Reply

Tags
nested table, search nested table

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Formatting Nested Tables LBruce Word VBA 3 01-20-2020 09:07 AM
Search in Nested Tables to Delete Rows Delete all empty rows in all tables fbucaram Word VBA 6 01-05-2018 03:04 PM
Nested tables. Count rows NevilleT Word VBA 9 05-10-2017 05:22 AM
Search in Nested Tables to Delete Rows Macro to delete all empty rows from all tables braddgood Word VBA 15 10-02-2015 01:54 PM
Nested vlookup with varable tables! Dave Jones Excel 0 08-30-2012 09:15 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 08:48 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft