|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Wish to delete letters to the right of the cursor in a Table cell, but whole cell is being deleted
Hi All,
I have a large table, with some cells having a character "#" in between some of the texts. For eg, there is a special character "#" in the text FESM#RAMAN. I wish to find the # character and then delete ALL the letters to the right of it in that particular cell, i.e. I wish to be left with FESEM only in the cell. I have recorded a macro for this, which goes to the home (Ctrl+Home), finds #, then the macro performs "del" operation repeatedly (i.e. the first instance of "del" deletes the # since it is selected, and then the subsequent instances of "del" delete the following letters R,A,M etc. My problem is that I do not know before-hand exactly how many characters are there following #, although I can safely assume it to be 20 characters max, so in the macro the "del" operation is repeated about 20 times. However, I observe that (say for the above case FESM#RAMAN, after deleting # and the letters R,A,M,A,N, it deletes the whole content of the cell, leaving a blank cell behind. This problem is not apparent when I record the macro or when I replicate the process manually (as the cursor reaches in the end and the subsequent "del" have no effect), but when this recorded macro is run, the next press of del deletes the contents to the left also (within the cell). This is came to know by stepping into macro and using F8. Please suggest (preferably using record-macro operation as I am not conversant with VBA) how to delete only the desired letters, leaving, for eg, FESM intact. [Version: Office 365, Windows 10] |
#2
|
||||
|
||||
You could use a macro like:
Code:
Sub Demo() Application.ScreenUpdating = False Dim Rng As Range, i As Long With ActiveDocument.Tables(1) Set Rng = .Range With .Range With .Find .ClearFormatting .Replacement.ClearFormatting .Text = "#" .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 = True If .InRange(Rng) = False Then Exit Do .MoveEnd Unit:=wdCell, Count:=1 .Text = vbNullString .Collapse wdCollapseEnd .Find.Execute Loop End With End With Application.ScreenUpdating = True End Sub
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#3
|
|||
|
|||
Dear Paul,
Thanks a lot, it works like a charm. My purpose is solved.... almost. Could you please provide me a slightly modified version, which can do the same for only the first occurence of #, instead of doing it for all occurences in one go? Since the problem I mentioned was in fact a part of the bigger problem and I need to do some additional formatting everytime after deleting the text to the right of # in the cell. I can repeat this macro multiple number of times. Secondly, I am quite worried that the macro which I had written (recorded) earlier by myself did not work the way I wanted. The worry is mainly due to the fact that what seems working when I use the manual sequence of operations using keyboard/mouse (and even when recording the macro), doesn't work the same way when the recorded macro is run. That is, when I press the del key repeatedly in a table cell in the middle of some text, the letters to the left should not be deleted, but running the macro actually deletes the letters to the left also. I would be thankful if you can throw some light (and possible remedy), since I mainly rely on recording macros rather than writing scripts as I am not conversant with that. Thank you |
#4
|
||||
|
||||
You could simply replace:
Code:
Do While .Find.Found = True If .InRange(Rng) = False Then Exit Do .MoveEnd Unit:=wdCell, Count:=1 .Text = vbNullString .Collapse wdCollapseEnd .Find.Execute Loop Code:
If .Find.Found = True Then .MoveEnd Unit:=wdCell, Count:=1 .Text = vbNullString End if Quote:
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#5
|
|||
|
|||
Thanks again.
Not sure if I am doing sth wrong, but get an error message in the 'If' statement that you provided as a replacement, my macro is like this now: Code:
Sub Macro15() Application.ScreenUpdating = False Dim Rng As Range, i As Long With ActiveDocument.Tables(1) Set Rng = .Range With .Range With .Find .ClearFormatting .Replacement.ClearFormatting .Text = "#" .Replacement.Text = "" .Forward = True .Wrap = wdFindStop .Format = False .MatchCase = False .MatchWholeWord = False .MatchWildcards = False .MatchSoundsLike = False .MatchAllWordForms = False .Execute End With If .Find.Found = True .MoveEnd Unit:=wdCell, Count:=1 .Text = vbNullString End If End With End With Application.ScreenUpdating = True End Sub |
#6
|
||||
|
||||
There should be a 'Then' after 'If .Find.Found = True'.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#7
|
|||
|
|||
Thanks a lot !....... don't know how come I missed this word while copying and pasting...., works now !
|
#8
|
|||
|
|||
As per the earlier comments,
Originally Posted by vjvj123: I am quite worried that the macro which I had written (recorded) earlier by myself did not work the way I wanted. The worry is mainly due to the fact that what seems working when I use the manual sequence of operations using keyboard/mouse (and even when recording the macro), doesn't work the same way when the recorded macro is run. That is, when I press the del key repeatedly in a table cell in the middle of some text, the letters to the left should not be deleted, but running the macro actually deletes the letters to the left also. I would be thankful if you can throw some light (and possible remedy), since I mainly rely on recording macros rather than writing scripts as I am not conversant with that. and your reply,... Since you haven't posted your own code, it's impossible to know for sure what the issue is.[/QUOTE] I hereby post my code (generated through recording macro). The text in the table cell under consideration as an example is NMR#Mass. Code:
Sub Macro16() ' ' Macro16 Macro ' ' Selection.HomeKey Unit:=wdStory Selection.Find.ClearFormatting Selection.Find.Replacement.ClearFormatting With Selection.Find .Text = "#" .Replacement.Text = "" .Forward = True .Wrap = wdFindContinue .Format = False .MatchCase = False .MatchWholeWord = False .MatchWildcards = False .MatchSoundsLike = False .MatchAllWordForms = False End With Selection.Find.Execute Selection.Delete Unit:=wdCharacter, Count:=1 Selection.Delete Unit:=wdCharacter, Count:=1 Selection.Delete Unit:=wdCharacter, Count:=1 Selection.Delete Unit:=wdCharacter, Count:=1 Selection.Delete Unit:=wdCharacter, Count:=1 Selection.Delete Unit:=wdCharacter, Count:=1 Selection.Delete Unit:=wdCharacter, Count:=1 Selection.Delete Unit:=wdCharacter, Count:=1 Selection.Delete Unit:=wdCharacter, Count:=1 Selection.Delete Unit:=wdCharacter, Count:=1 Selection.Delete Unit:=wdCharacter, Count:=1 Selection.Delete Unit:=wdCharacter, Count:=1 Selection.Delete Unit:=wdCharacter, Count:=1 Selection.Delete Unit:=wdCharacter, Count:=1 Selection.Delete Unit:=wdCharacter, Count:=1 Selection.Delete Unit:=wdCharacter, Count:=1 Selection.Delete Unit:=wdCharacter, Count:=1 End Sub Will look forward for any comments on why running macro deletes everything in the cell, whereas it is intended to delete text only to the right of # Thank you Last edited by macropod; 09-24-2018 at 03:52 PM. Reason: Added code tags & formatting |
#9
|
||||
|
||||
The problem with what you recorded is that you simply keep deleting characters until you've deleted 17 of them. What you should have recorded is:
Code:
Sub Macro1() Selection.Find.ClearFormatting Selection.Find.Replacement.ClearFormatting With Selection.Find .Text = "#" .Replacement.Text = "" .Forward = True .Wrap = wdFindContinue .Format = False .MatchCase = False .MatchWholeWord = False .MatchWildcards = False .MatchSoundsLike = False .MatchAllWordForms = False End With Selection.Find.Execute Selection.MoveRight Unit:=wdWord, Count:=1, Extend:=wdExtend Selection.Delete End Sub Selection.MoveRight Unit:=wdWord, Count:=1, Extend:=wdExtend is what you get via Ctrl-Shift-RightArrow, which doesn't require you to know how many characters there are and, moreover, spans everything until the end of the line.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#10
|
|||
|
|||
Quote:
[eg, the text like abcd#pqrs#uvwx ] Please let me know if I have followed your suggestion correctly, and if yes the possible remedy. |
#11
|
||||
|
||||
Quote:
Why are you persisting with this when I already gave you a solution in posts #2 & #4?
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#12
|
|||
|
|||
Thanks for the solution in posts #2 and #4 which works. However posted the query since I am interested in this due to curiosity and academic interest (learning) since it worries me what seems to work while recording macro doesn't work the same way while running the macro. Moreover, if it works, this is easier for me to perform it by recording macro rather than by writing script.
|
#13
|
||||
|
||||
There are many actions you cannot record using the macro recorder. If you're determined to limit yourself to what the macro recorder can record, then you're equally determined to live within its limitations and to not be more productive.
FWIW, you could find the whole of the content to the right of the first # with a wildcard Find expression: Find = #[!^13]{1,} provided there aren't multiple paragraphs in the cell; otherwise it'll find just to the character before the end of the first paragraph containing a #.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
Tags |
delete text toright, macro, table cell |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
cell alignment gets deleted when using backspace in a cell | saandahn | Word Tables | 4 | 01-08-2018 09:27 AM |
How to delete unused cell remaining at end of a block of deleted data | SKEETER | Excel | 2 | 10-24-2017 07:06 AM |
How can I delete spaces & lines in a table cell | mrayncrental | Word VBA | 3 | 10-20-2014 07:09 PM |
Delete table if cell has data | So New2 This | Word VBA | 1 | 05-06-2013 09:00 PM |
How can I delete the content of a cell in column if the cell value is more than 1000? | Learner7 | Excel | 2 | 06-27-2011 05:44 AM |