Deleting Rows in a range if they contain certain text

HellNoJello

New Member
Joined
Sep 21, 2005
Messages
9
I want to delete all the rows in column A if they contain the text "FILENET TRANSMITTAL FORM" How can I do this?

Thanks.
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

Datsmart

Well-known Member
Joined
Jun 19, 2003
Messages
7,985
This will delete all Rows that contain your text in column A.
Code:
Sub DeleteRowByText()
    Dim rng As Range, rng2 As Range
    Set rng = Range("A65536").End(xlUp)
    Do Until rng.Row = 2
        Set rng2 = rng.Offset(-1)
        If rng = "FILENET TRANSMITTAL FORM" Then rng.EntireRow.Delete
        Set rng = rng2
    Loop
End Sub
 

Domski

Well-known Member
Joined
Jan 18, 2005
Messages
7,292
You could also use Autofilter and select the Custom...contains option and then delete the resulting rows.
 

HellNoJello

New Member
Joined
Sep 21, 2005
Messages
9
Hmm I couldn't get that to work, Datsmart. All I was supposed to do is cut and paste that into the VBA code of the excel sheet right? Is there something else I needed to do?
 

Datsmart

Well-known Member
Joined
Jun 19, 2003
Messages
7,985

ADVERTISEMENT

To use the posted code; (by Eric Van Geit)
Start the Visual Basic Editor (via Menu Tools, Macro or press ALT+F11).
On the Insert menu in the VBE, click Module. (if necessary)
In the module (the white area at the right), paste the your code

How to create a button and assign a macro to it:

If you don't already have the "Forms" toolbar active, click on Tools>Customize>Toolbars and check "Forms". The toolbar will appear on your screen; drag it down to the bottom of your screen to anchor it.

Click on the Button icon, and drag out a button on the screen. When you release the left mouse button, an "Assign Macro" dialogue box will appear. Highlight whatever macro you want, and click OK. The macro will now be assigned to that button.

You can also run the code by hitting Alt-F8 and selecting the macro name.
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
Hi,

John, may I add some remarks ?
It's good practice to avoid
1. loops if possible
2. using "ranges" (Set rng = ...), because this is a very "heavy" - read "slow" - way of processing: each time you set a range, a whole bunch of properties is stored (check your "local variables" window while stepping through the code)

using autofilter in code is fast
Code:
Sub deleterows_autofilter()
'header in A1
'data without gaps

Application.ScreenUpdating = False
Range("A1").AutoFilter Field:=1, Criteria1:="FILENET TRANSMITTAL FORM"
Range("A2:A" & Range("A1").End(xlDown).Row).EntireRow.Delete
ActiveSheet.AutoFilterMode = False
Application.ScreenUpdating = True
End Sub
kind regards,
Erik
 

Datsmart

Well-known Member
Joined
Jun 19, 2003
Messages
7,985

ADVERTISEMENT

Thanks for the suggestions Eric.
I have used AutoFilter in the past but it always ended up deleting the first row, which was not desired. I see your code eliminates that problem.
Thanks again.
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
you're welcome, John,

sometimes autofilter won't work (f.i. when there are "gaps" in the data)
then you can use something like
Code:
Sub delete_range()
Dim rng As Range
Dim LR As Long      'last row

Columns(1).Insert
Application.ScreenUpdating = False

Set rng = Range(Cells(2, 1), Cells(Rows.Count, 2).End(xlUp).Offset(0, -1))
    With rng
        .FormulaR1C1 = "=IF(RC[1]=""FILENET TRANSMITTAL FORM"","""",1)"
        .Copy
        .PasteSpecial Paste:=xlValues
        .EntireRow.Sort Key1:=rng(1), Order1:=xlDescending, Header:=xlNo
        LR = .SpecialCells(xlCellTypeConstants, 2).Cells.Count + 1
        .EntireColumn.Delete
        Range("A2:A" & LR).Delete
    End With
    
Application.ScreenUpdating = True

End Sub
best regards,
Erik
 

jabba9

New Member
Joined
May 18, 2004
Messages
42
erik

i'm trying to do something similar

i need to delete an entire row (not just Col A) if Col A contains "Colorado"

i'm trying to amend your code, but i thought i'd ask also

thanks

Mike
 

HellNoJello

New Member
Joined
Sep 21, 2005
Messages
9
Thanks - I have one more for you guys

Ok...now that I've gotten rid of all the rows I don't want. Is there any way to transpose my lines of data automatically. Lets say I have a column of data in colum A, what I want to is for the first seven rows of date to be transposed to rows 1-7 will all be in there own colum (Like cell A1 goes into cell B1, Cell A2 goes into Cell C1, A3 to D1 and so on. Then I want rows 8-14 to all be transposed into their own columns and match up with what I just transposed from lines 1-7. And so on until I get to the end of my data in column A. Is there any easy way to do this? I appreciate all the help!

Thanks!
 

Forum statistics

Threads
1,136,272
Messages
5,674,749
Members
419,525
Latest member
helensesc

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
Top