Deleting Rows in a range if they contain certain text

[HellNoJello]

I want to delete all the rows in column A if they contain the text "FILENET TRANSMITTAL FORM" How can I do this?

Thanks.

 

[Datsmart]

This will delete all Rows that contain your text in column A.

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]

You could also use Autofilter and select the Custom...contains option and then delete the resulting rows.

 

[HellNoJello]

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]

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]

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

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]

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]

you're welcome, John,

sometimes autofilter won't work (f.i. when there are "gaps" in the data)
then you can use something like

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]

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]

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!