Thread: How do I delete rows by shade in WORD

Hi all,


I have a word doc that has many rows and uses multiple different color shades (background fills) to identify the rows. I am trying to create a macro that will select and delete all of the rows which are one color and running into difficulties.

Any Suggestions?

Thanks!

The following might get you started. This probably needs lots of work to finetune for your specific needs. I use an input box and request for typical color responses: yellow, blue, red, green, pink, gray. I force the response to small case so to remove any case variability. I prompt for these specific colors; add more to InputBox & Case options if you need them. I store the response to a string, then use the Select Case to convert to a number. Then use the .FIND.EXECUTE method to find any highlight. Then, if .FOUND object has .HighlightColorIndex = ColorNum variable, then delete it. Otherwise, if nothing found, then tell user macro is done. If something is found & deleted, then loop back and search again.

To finetune this for your needs, use the Object Browser (F2) & put cursor on any property or method and hit F1 get the built-in HELP & EXAMPLES to guide you. Hit F7 to get back to code. Hit F8 in VB editor to watch code execute line-by-line; this helps to troubleshoot. Save often and since your deleting stuff, save file as a special test version until you get it right. Good luck.

Sub DeleteColor()
Dim rngTemp As Range, ColorWord As String, ColorNum As Long
Set rngTemp = ActiveDocument.Range(0, 0)        'Put cursor at very beginning of document to start
rngTemp.Select
ColorWord = InputBox("Type color to delete. Options are ..." & Chr(13) & Chr(13) & _
                 "yellow, blue, red, green, pink, gray", _
                 "User Request for Color to Delete", _
                 "Type color to delete here ...", _
                 9000, 1500)
  If Len(ColorWord) = 0 Then Exit Sub          'If user hit CANCEL, quit now
  ColorWord = LCase(ColorWord)
Select Case ColorWord
 Case "yellow"
      ColorNum = 7
 Case "blue"
      ColorNum = 3
 Case "red"
      ColorNum = 6
 Case "green"
      ColorNum = 4
 Case "pink"
      ColorNum = 5
 Case "gray"
      ColorNum = 16
 End Select

Start_Highlight_Search:
With rngTemp.Find
    .ClearFormatting
    .Highlight = True
    .Execute Forward:=True, FindText:="", Format:=True
    If .Found = True Then
      rngTemp.Select
     Else: MsgBox "No More Highlights Found", vbInformation, "Macro Done Message"
     Exit Sub
    End If
    If Selection.Range.HighlightColorIndex = ColorNum Then Selection.Delete
 End With
 GoTo Start_Highlight_Search
End Sub

If you havent guessed this is my first time working with the code and not just recording Macros. I'm a little confused in a couple spots as I want to find any cell in the table that has the wdColorGray35 shading and then to remove the row it is in. I might have butchered the code a little bit:

Sub DeleteColor()
Dim rngTemp As Range, ColorWord As String, ColorNum As Long
Set rngTemp = ActiveDocument.Range(0, 0)        'Put cursor at very beginning of document to start
rngTemp.Select
ColorWord = InputBox("Type color to delete. Options are ..." & Chr(13) & Chr(13) & _
                 "red, gray", _
                 "User Request for Color to Delete", _
                 "Type color to delete here ...", _
                 9000, 1500)
  If Len(ColorWord) = 0 Then Exit Sub          'If user hit CANCEL, quit now
  ColorWord = LCase(ColorWord)
Select Case ColorWord
 Case "red"
      ColorNum = 6
 Case "gray"
      ColorNum = wdColorGray35
 End Select

Start_Highlight_Search:
With rngTemp.Find
    .ClearFormatting
    .Cell                                               'I need something here to check the cell shading
    .Execute Forward:=True, FindText:="", Format:=True
    If .Found = True Then
      rngTemp.Select
     Else: MsgBox "No More Deactive", vbInformation, "Macro Done Message"
     Exit Sub
    End If
    If Selection.Range.Shading.BackgroundPatternColorIndex = ColorWord Then Selection.Delete 'Need this to be delete row
 End With
 GoTo Start_Highlight_Search
End Sub

Completely different approach here! The FIND approach won't work as searching for text highlighting will NOT find table cells that are shaded (two completely different things).
Top part is the same, but bottom part is different; actually pretty simple. Use the 'For Each' loops to go thru all the tables, and then same approach to go thru all the cells in the given table. Check for desired shading in any cell, if found, then delete that cell's row. This assumes you are using the BackgroundPattern color not the ForegroundPattern color. If wrong, then change or add 'OR' statement if you might also be using foregroundpattern color. I added a little counter to report # of deletions. Tweak as necessary.

Advice on Macros: Understanding "object" language is NOT intuitive. It took me months to start to understand any sort of logical pattern behind the commands. You have to start to really understand the Object Browser (F2 in the VB editor) which is a good guide to help to understand the "pattern". In the Object Browser, right click & click on Group Members; I like this 'view' better. Spend some time and page down thru the objects & commands, and see the various property & method options.
Lastly, consider buying a VBA Help book. Don't buy one of those HUGE books; its more than you need. Not a lot of good Word VBA books out there; but many good Excel VBA books. You'll learn a lot reading an Excel VBA book (although the commands & options differ from Word VBA at times). I bought Excel Programming by Jinjer Simon, Wiley Publishing, 2nd Edition (LINK). It's pretty good ... or buy any book that is similar to this (i.e. the DUMMY series for example). Good luck!

Sub DeleteColor()
Dim ColorWord As String, ColorNum As Long
Dim Tbl As Table, Cel As Cell, DelCount As Long
ActiveDocument.Range(0, 0).Select        'Put cursor at very beginning of document to start
ColorWord = InputBox("Type color to delete. Options are ..." & Chr(13) & Chr(13) & _
                 "red, gray", _
                 "User Request for Color to Delete", _
                 "Type color to delete here ...", _
                 9000, 1500)
  If Len(ColorWord) = 0 Then Exit Sub    'If user hit CANCEL, quit now
  ColorWord = LCase(ColorWord)
Select Case ColorWord
 Case "red"
      ColorNum = 6
 Case "gray"
      ColorNum = wdColorGray35
 End Select

 For Each Tbl In ActiveDocument.Tables
  For Each Cel In Tbl.Range.Cells
    Cel.Select            'This is not necessary, but helps you understand if you
                          'single step the macro by hitting 'F8' inside VB editor
    If Cel.Shading.BackgroundPatternColor = ColorNum Then
     Cel.Row.Delete       'Now delete row that cells belongs to
     DelCount = DelCount + 1
    End If
  Next Cel
 Next Tbl
 MsgBox "No More text found in table w/ desired color." & Chr(13) & Chr(13) & _
        DelCount & " total rows were deleted from the document tables!", _
           vbInformation, "Macro Done Message"
End Sub