Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 03-14-2013, 04:07 PM
netchie netchie is offline Remove Red macro Windows XP Remove Red macro Office 2007
Novice
Remove Red macro
 
Join Date: Jun 2011
Posts: 24
netchie is on a distinguished road
Default Remove Red macro

Hello,

I have a macro where it will remove all colors that are Red and any data of it and will not include the next different color and their data. The problem lately I'm noticing that the macro is also removing the next color after Red and some of its data. Can you please check what I've missed on this? Here is the print screen (I don't know how to upload the word file here) --> http://s14.postimage.org/olbv9g3up/Test.jpg . The ones in yellow shades should be removed but it should not include the next color (ex: Blue and its data) but for some reason the macro below is also removing them
Code:
'Test Remove Red Color Sub RemoveRed() Application.ScreenUpdating = False Dim i As Long, Rng As Range, sVpos As Single With ActiveDocument.Range With .Find .ClearFormatting .Replacement.ClearFormatting .Text = "Red" .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, "Red", 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


Last edited by macropod; 03-14-2013 at 10:46 PM. Reason: Added code tags & formatting
Reply With Quote
  #2  
Old 03-14-2013, 11:36 PM
macropod's Avatar
macropod macropod is offline Remove Red macro Windows 7 64bit Remove Red macro Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,962
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

As explained in the thread you got the code from:
https://www.msofficeforums.com/word-...fic-words.html
Quote:
The macro ... 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.
In this case, you might need to reduce the 36 to, say, 33 or 30.

Instead of getting reports that don't really meet you needs and require considerable massaging afterwards, I really do think you should be looking at getting some new reports that actually reflect your requirements.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #3  
Old 03-15-2013, 12:25 PM
netchie netchie is offline Remove Red macro Windows XP Remove Red macro Office 2007
Novice
Remove Red macro
 
Join Date: Jun 2011
Posts: 24
netchie is on a distinguished road
Default

hi macropod,

I'm glad it's an easy fix.

Thanks again!

Netchie
Reply With Quote
  #4  
Old 03-15-2013, 12:37 PM
netchie netchie is offline Remove Red macro Windows XP Remove Red macro Office 2007
Novice
Remove Red macro
 
Join Date: Jun 2011
Posts: 24
netchie is on a distinguished road
Default

Hi macropod,

If I make it 30, it's possible that some of data below Red won't be removed. Sometimes Red has 3 rows below and sometimes 2. If let's say the number I changed can remove 3 columns below RED and the report has only 2 columns for RED then it will automatically removed the third column which is BLUE.

How can I add the if and then where if it's RED and it has "1" or "2" or "3" below it then they need to be removed?
Reply With Quote
  #5  
Old 03-15-2013, 03:12 PM
macropod's Avatar
macropod macropod is offline Remove Red macro Windows 7 64bit Remove Red macro Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,962
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

It really isn't practical to try to test what's in other frames nearby, especially when there could be more than one of them. There is a limit to what can be achieved in this situation and you're already pushing that limit.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #6  
Old 03-15-2013, 04:22 PM
netchie netchie is offline Remove Red macro Windows XP Remove Red macro Office 2007
Novice
Remove Red macro
 
Join Date: Jun 2011
Posts: 24
netchie is on a distinguished road
Default

I see; well it worked so far so thank you again for checking this for me and I hope you won't sick and tired of checking my Word issues.

Have a great day!

Thanks,
Netchie
Reply With Quote
Reply



Similar Threads
Thread Thread Starter Forum Replies Last Post
Remove Red macro Macro to remove blank column netchie Word VBA 4 03-14-2013 02:22 PM
Remove Red macro Need VBA For Macro On How To Remove Specific Words netchie Word VBA 6 08-28-2012 03:37 PM
Remove Red macro Macro to remove MacroButtons ilkks Word VBA 3 05-22-2011 11:03 PM
Remove Red macro Need help on Macro 03- Find text - if text is blank then remove line simpleonline1234 Word VBA 1 02-25-2011 02:28 AM
Macro to remove duplicates in Refrences list HowardC Word VBA 0 05-20-2010 09:57 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 04:35 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