WORD: Deleting all text that begins with a certain string

socialstudies2007

New Member
Joined
Mar 16, 2017
Messages
4
I'm trying to create a macro that will delete all lines of text that start with a specified string.

For example, each such line begins with "Category" and then is followed with a random length string of additional characters. All these lines do end with the same string, however: "End"

I'm not having any luck crafting this macro on my own.

I would appreciate any assistance anyone could render!

Thank you very much,

A currently confused teacher
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
Re: Deleting all text that begins with a certain string

Are all of these values in the same column? Also does the range fluctuate or is it always the same amount of cells you need to look at? Are you using a table or a standard range of cells?

You can always use a helper column with the formula: =IF(LEFT(A1,8)="Category","Delete",""). This will mark wich ones apply and then you can just filter and delete. I am more than happy to assist with the VBA if you can give me a little more information!
 
Upvote 0
Re: Deleting all text that begins with a certain string

Try this VBA code:
Code:
Sub MyDeleteRows()

    Dim lrow As Long
    Dim r As Long
    
    Application.ScreenUpdating = False
    
'   Find last row in column A with entries
    lrow = Cells(Rows.Count, "A").End(xlUp).Row
    
'   Loop through all rows in column A going backwards
    For r = lrow To 1 Step -1
'       See if entry begins with "Category" and ends with "End"
        If LCase(Left(Cells(r, "A"), 8)) = "category" And LCase(Right(Cells(r, "A"), 3)) = "end" Then
'           Delete row
            Rows(r).Delete
        End If
    Next r
    
    Application.ScreenUpdating = True
    
End Sub
 
Upvote 0
Re: Deleting all text that begins with a certain string

This is actually a macro for a word document. I thought VBA would transfer across Microsoft Office products pretty seamlessly... I was thinking I just needed the correct command and I could put it in a loop to repeat, and that repeating line of code shouldn't have any row or cell etc references.

Your help is very much appreciated!

I look forward to your reply.

Thanks!


Are all of these values in the same column? Also does the range fluctuate or is it always the same amount of cells you need to look at? Are you using a table or a standard range of cells?

You can always use a helper column with the formula: =IF(LEFT(A1,8)="Category","Delete",""). This will mark wich ones apply and then you can just filter and delete. I am more than happy to assist with the VBA if you can give me a little more information!
 
Upvote 0
Re: Deleting all text that begins with a certain string

This is actually a macro for a word document. I thought VBA would transfer across Microsoft Office products pretty seamlessly
That is something you want to be sure to mention in your title and post, as it is different (Word does not have cells/ranges like Excel does).

I have added "WORD" to your title and moved this to the "General Excel Discussion & Other Questions" forum instead of the "Excel Questions" forum. That is a better place for it, and we have a Word expert here who often checks that forum for Word questions.
 
Upvote 0
Re: Deleting all text that begins with a certain string

You don't really need a macro for that in Word. In Word, it can be done with a wildcard Find/Replace, where:
Find = ^13Category[!^13]@End^13
Replace = ^p
 
Upvote 0

Forum statistics

Threads
1,213,554
Messages
6,114,280
Members
448,562
Latest member
Flashbond

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top