Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 08-08-2012, 01:44 PM
netchie netchie is offline Need VBA For Macro On How To Remove Specific Words Windows XP Need VBA For Macro On How To Remove Specific Words Office 2007
Novice
Need VBA For Macro On How To Remove Specific Words
 
Join Date: Jun 2011
Posts: 24
netchie is on a distinguished road
Unhappy Need VBA For Macro On How To Remove Specific Words

Hi,

I have tables on my Word doc and I wanted to create a macro to remove rows that have "YES COLOR" and it's next two rows as well. What command I need to put on VBA?

Can you please help me?

Thanks,


netchie
Reply With Quote
  #2  
Old 08-08-2012, 04:55 PM
macropod's Avatar
macropod macropod is offline Need VBA For Macro On How To Remove Specific Words Windows 7 64bit Need VBA For Macro On How To Remove Specific Words Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,963
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

Hi netchie,

Whether you need a macro, and what the code should be all depends on what is meant by the 'next two rows'. If all the rows are in the same paragraph and there's nothing else past them, the Find expression would be quite different to what it would be if they are separate paragraphs or if there's more text beyond what is to be deleted in the same paragraph(s).

Can you attach a document to a post with some representative data (delete anything sensitive)? You do this via the paperclip symbol on the 'Go Advanced' tab.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #3  
Old 08-24-2012, 03:25 PM
netchie netchie is offline Need VBA For Macro On How To Remove Specific Words Windows XP Need VBA For Macro On How To Remove Specific Words Office 2007
Novice
Need VBA For Macro On How To Remove Specific Words
 
Join Date: Jun 2011
Posts: 24
netchie is on a distinguished road
Default

Hi Macropod,

I'm sorry for late reply. Please see attached for dummy table (well actually it's not a table but text boxes). So I'm having a hard time to create VBA where I can remove anything that has "No Color" (please see in yellow shades) and if it has more data on it's next rows, they need to be deleted as well.
Attached Files
File Type: docx Test Updated.docx (164.1 KB, 13 views)
Reply With Quote
  #4  
Old 08-24-2012, 04:28 PM
macropod's Avatar
macropod macropod is offline Need VBA For Macro On How To Remove Specific Words Windows 7 64bit Need VBA For Macro On How To Remove Specific Words Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,963
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

Hi netchie,

Your document looks like something generated by a low-end OCR package - it's all just a series of textboxes and lines made to look like a table.

Trying to do anything programmatically with that might be good as a character-building exercise, but not if you want to actually achieve anything.

Probably what you need is an application that can save the data as a PDF, Word table (in the true sense) or Excel workbook and, if as a PDF, another application that can convert tabulated PDF content to a Word table or Excel.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #5  
Old 08-24-2012, 05:20 PM
netchie netchie is offline Need VBA For Macro On How To Remove Specific Words Windows XP Need VBA For Macro On How To Remove Specific Words Office 2007
Novice
Need VBA For Macro On How To Remove Specific Words
 
Join Date: Jun 2011
Posts: 24
netchie is on a distinguished road
Default

The problem is I need to use it in Word doc and not convert it to others. Data came from Crystal by the way.

Thi sis what I have but what I need is to remove lines that have "No Color" but with zero values. If they have 1 or other values, i should not be removed. Also, as I've said i it has next row(s) data, it should be removed as well BUT only with zero values.

What I have is this only for No Color:
Code:
'Remove the No Color line
Sub NoCorrelateRemove()
Dim lineRange As Range
Dim searchRange As Range
'Search the entire document
Set searchRange = ActiveDocument.Content
Do
  'Find the No Color line
  With searchRange.Find
    .ClearFormatting
    .Wrap = wdFindStop
    .Forward = True
    .Text = "No Color"
    .Execute
    If Not .Found Then
      Exit Do
    End If
  End With
  'create a range that covers this entire line
  Set lineRange = ActiveDocument.Range(searchRange.Start = 1, searchRange.End = 5)
  lineRange.Expand wdParagraph
  lineRange.Delete
Loop
End Sub

Last edited by macropod; 08-24-2012 at 05:32 PM. Reason: Added code tags & formatting
Reply With Quote
  #6  
Old 08-24-2012, 07:55 PM
macropod's Avatar
macropod macropod is offline Need VBA For Macro On How To Remove Specific Words Windows 7 64bit Need VBA For Macro On How To Remove Specific Words Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,963
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

Hi netchie,

That approach isn't going to work because the content you're looking for isn't in Word's document layer, but in a series of independent frames overlaying it. So, although you can find the text, you can't do anything with the related content.

What you need to do after finding the text is to check every frame on that page and, if it has the text you're looking for, work out where all the other frames are that relate to it visually so that you can delete them. Ultimately, it's all trial and error.

The macro below looks like it will do what you want with the sample you posted. Basically, it deletes any frames whose tops are located within -4 to +36 points vertically from the frame containing the found text. You may need to adjust those values.
Code:
Sub NoCorrelateRemove()
Application.ScreenUpdating = False
Dim i As Long, Rng As Range, sVpos As Single
With ActiveDocument.Range
  With .Find
    .ClearFormatting
    .Replacement.ClearFormatting
    .Text = "No Color"
    .Replacement.Text = ""
    .Forward = True
    .Wrap = wdFindStop
    .Format = False
    .MatchCase = False
    .MatchWholeWord = False
    .MatchWildcards = False
    .MatchSoundsLike = False
    .MatchAllWordForms = False
    .Execute
  End With
  Do While .Find.Found
    i = .Duplicate.Information(wdActiveEndPageNumber)
  Set Rng = ActiveDocument.GoTo(What:=wdGoToPage, Name:=i)
  Set Rng = Rng.GoTo(What:=wdGoToBookmark, Name:="\page")
    sVpos = 0
    With Rng
      For i = 1 To .Frames.Count
        If InStr(1, .Frames(i).Range.Text, "no color", vbTextCompare) Then
          .Frames(i).RelativeVerticalPosition = wdRelativeVerticalPositionPage
          sVpos = .Frames(i).VerticalPosition - 4
          Exit For
        End If
      Next
      For i = .Frames.Count To 1 Step -1
        With .Frames(i)
          If .VerticalPosition > (sVpos) Then
            If .VerticalPosition < (sVpos + 36) Then
              .Cut
            End If
          End If
        End With
      Next
    End With
    .Collapse wdCollapseEnd
    .Find.Execute
  Loop
End With
Application.ScreenUpdating = True
End Sub
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #7  
Old 08-28-2012, 03:37 PM
netchie netchie is offline Need VBA For Macro On How To Remove Specific Words Windows XP Need VBA For Macro On How To Remove Specific Words Office 2007
Novice
Need VBA For Macro On How To Remove Specific Words
 
Join Date: Jun 2011
Posts: 24
netchie is on a distinguished road
Default

! It worked!

Thanks so much macropod. You ROCK!!!
Reply With Quote
Reply



Similar Threads
Thread Thread Starter Forum Replies Last Post
Need VBA For Macro On How To Remove Specific Words Macro to replace one specific heading style with another ubns Word VBA 44 09-04-2012 08:17 PM
can I run a macro when I open a specific doc. shreked Word 8 01-12-2012 03:36 AM
Need VBA For Macro On How To Remove Specific Words Macro for highlighting specific number of words icsjohn Word VBA 2 12-07-2011 06:44 PM
Need VBA For Macro On How To Remove Specific Words Macro to remove MacroButtons ilkks Word VBA 3 05-22-2011 11:03 PM
Remove specific recipients while replying to all McFerra Outlook 0 12-22-2010 10:13 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 07:54 PM.


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