#1
|
|||
|
|||
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 |
#2
|
||||
|
||||
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] |
#3
|
|||
|
|||
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. |
#4
|
||||
|
||||
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] |
#5
|
|||
|
|||
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 |
#6
|
||||
|
||||
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] |
#7
|
|||
|
|||
! It worked!
Thanks so much macropod. You ROCK!!! |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
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 |
Macro for highlighting specific number of words | icsjohn | Word VBA | 2 | 12-07-2011 06:44 PM |
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 |