Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 09-23-2018, 09:35 AM
vjvj123 vjvj123 is offline Wish to delete letters to the right of the cursor in a Table cell, but whole cell is being deleted Windows 10 Wish to delete letters to the right of the cursor in a Table cell, but whole cell is being deleted Office 2016
Novice
Wish to delete letters to the right of the cursor in a Table cell, but whole cell is being deleted
 
Join Date: Sep 2018
Posts: 11
vjvj123 is on a distinguished road
Default 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]
Reply With Quote
  #2  
Old 09-23-2018, 03:46 PM
macropod's Avatar
macropod macropod is offline Wish to delete letters to the right of the cursor in a Table cell, but whole cell is being deleted Windows 7 64bit Wish to delete letters to the right of the cursor in a Table cell, but whole cell is being deleted Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
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

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
Note: There is no difference between a macro and VBA. When recorded, macros use VBA. You wouldn't be able to record the above macro, however. The above macro assumes only the first table in the document is to be processed.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #3  
Old 09-23-2018, 07:52 PM
vjvj123 vjvj123 is offline Wish to delete letters to the right of the cursor in a Table cell, but whole cell is being deleted Windows 10 Wish to delete letters to the right of the cursor in a Table cell, but whole cell is being deleted Office 2016
Novice
Wish to delete letters to the right of the cursor in a Table cell, but whole cell is being deleted
 
Join Date: Sep 2018
Posts: 11
vjvj123 is on a distinguished road
Default

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
Reply With Quote
  #4  
Old 09-23-2018, 07:55 PM
macropod's Avatar
macropod macropod is offline Wish to delete letters to the right of the cursor in a Table cell, but whole cell is being deleted Windows 7 64bit Wish to delete letters to the right of the cursor in a Table cell, but whole cell is being deleted Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
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

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
With:
Code:
    If .Find.Found = True Then
      .MoveEnd Unit:=wdCell, Count:=1
      .Text = vbNullString
    End if
Quote:
Originally Posted by vjvj123 View Post
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.
Since you haven't posted your own code, it's impossible to know for sure what the issue is.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #5  
Old 09-23-2018, 08:04 PM
vjvj123 vjvj123 is offline Wish to delete letters to the right of the cursor in a Table cell, but whole cell is being deleted Windows 10 Wish to delete letters to the right of the cursor in a Table cell, but whole cell is being deleted Office 2016
Novice
Wish to delete letters to the right of the cursor in a Table cell, but whole cell is being deleted
 
Join Date: Sep 2018
Posts: 11
vjvj123 is on a distinguished road
Default

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
Reply With Quote
  #6  
Old 09-23-2018, 08:35 PM
macropod's Avatar
macropod macropod is offline Wish to delete letters to the right of the cursor in a Table cell, but whole cell is being deleted Windows 7 64bit Wish to delete letters to the right of the cursor in a Table cell, but whole cell is being deleted Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
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

There should be a 'Then' after 'If .Find.Found = True'.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #7  
Old 09-24-2018, 08:04 AM
vjvj123 vjvj123 is offline Wish to delete letters to the right of the cursor in a Table cell, but whole cell is being deleted Windows 10 Wish to delete letters to the right of the cursor in a Table cell, but whole cell is being deleted Office 2016
Novice
Wish to delete letters to the right of the cursor in a Table cell, but whole cell is being deleted
 
Join Date: Sep 2018
Posts: 11
vjvj123 is on a distinguished road
Default

Thanks a lot !....... don't know how come I missed this word while copying and pasting...., works now !
Reply With Quote
  #8  
Old 09-24-2018, 08:09 AM
vjvj123 vjvj123 is offline Wish to delete letters to the right of the cursor in a Table cell, but whole cell is being deleted Windows 10 Wish to delete letters to the right of the cursor in a Table cell, but whole cell is being deleted Office 2016
Novice
Wish to delete letters to the right of the cursor in a Table cell, but whole cell is being deleted
 
Join Date: Sep 2018
Posts: 11
vjvj123 is on a distinguished road
Default

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
Reply With Quote
  #9  
Old 09-24-2018, 03:51 PM
macropod's Avatar
macropod macropod is offline Wish to delete letters to the right of the cursor in a Table cell, but whole cell is being deleted Windows 7 64bit Wish to delete letters to the right of the cursor in a Table cell, but whole cell is being deleted Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
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

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
The line:
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]
Reply With Quote
  #10  
Old 09-26-2018, 08:58 AM
vjvj123 vjvj123 is offline Wish to delete letters to the right of the cursor in a Table cell, but whole cell is being deleted Windows 10 Wish to delete letters to the right of the cursor in a Table cell, but whole cell is being deleted Office 2016
Novice
Wish to delete letters to the right of the cursor in a Table cell, but whole cell is being deleted
 
Join Date: Sep 2018
Posts: 11
vjvj123 is on a distinguished road
Default

Quote:
Originally Posted by macropod View Post
The line:
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.
Dear Paul, Thanks, but I am still having problem, the reason being: when there is a special character like # in the text, using Ctrl-Shift-RightArrow doesn't actually select the text till end as expected and suggested by you, but rather only till the next occurence of the # in the same text string, thus compelling me to use Ctrl-Shift-RightArrow multiple times, which is effectively the same as using del key multiple times.
[eg, the text like abcd#pqrs#uvwx ]
Please let me know if I have followed your suggestion correctly, and if yes the possible remedy.
Reply With Quote
  #11  
Old 09-26-2018, 02:51 PM
macropod's Avatar
macropod macropod is offline Wish to delete letters to the right of the cursor in a Table cell, but whole cell is being deleted Windows 7 64bit Wish to delete letters to the right of the cursor in a Table cell, but whole cell is being deleted Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
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

Quote:
Originally Posted by vjvj123 View Post
when there is a special character like # in the text, using Ctrl-Shift-RightArrow doesn't actually select the text till end as expected and suggested by you, but rather only till the next occurence of the # in the same text string, thus compelling me to use Ctrl-Shift-RightArrow multiple times, which is effectively the same as using del key multiple times.
[eg, the text like abcd#pqrs#uvwx ]
Without wanting to place too fine a point on it, that's not the kind of example you gave in post #1. Using "Ctrl-Shift-RightArrow multiple times" is NOT "effectively the same as using del key multiple times".

Why are you persisting with this when I already gave you a solution in posts #2 & #4?
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #12  
Old 09-27-2018, 06:37 AM
vjvj123 vjvj123 is offline Wish to delete letters to the right of the cursor in a Table cell, but whole cell is being deleted Windows 10 Wish to delete letters to the right of the cursor in a Table cell, but whole cell is being deleted Office 2016
Novice
Wish to delete letters to the right of the cursor in a Table cell, but whole cell is being deleted
 
Join Date: Sep 2018
Posts: 11
vjvj123 is on a distinguished road
Default

Quote:
Originally Posted by macropod View Post
Why are you persisting with this when I already gave you a solution in posts #2 & #4?
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.
Reply With Quote
  #13  
Old 09-27-2018, 03:09 PM
macropod's Avatar
macropod macropod is offline Wish to delete letters to the right of the cursor in a Table cell, but whole cell is being deleted Windows 7 64bit Wish to delete letters to the right of the cursor in a Table cell, but whole cell is being deleted Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
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

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]
Reply With Quote
Reply

Tags
delete text toright, macro, table cell

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Wish to delete letters to the right of the cursor in a Table cell, but whole cell is being deleted 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
Wish to delete letters to the right of the cursor in a Table cell, but whole cell is being deleted 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

Other Forums: Access Forums

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