select and remove multiple rows based on content

[tmd63]

I have a simple request, but it is difficult to code.
I am using Excel 2010 and I have a single sheet spreadsheet.
What I need to do is find every instance of a piece of text ("Tape") in a column on the spreadsheet and delete the row that this text appears in. The text is on it's own (so is not part of a large piece of text) but may appear once only or 1,000 of times in a spreadsheet.
Can anyone help with some macro code that can perform this action please?

 

[dispelthemyth]

Try the below (Which checked column B and ignores the case of the text)

Sub DeleteRows()
Dim LRow As Long


Application.ScreenUpdating = False
    
    For LRow = Range("B" & Rows.Count).End(xlUp).Row To 2 Step -1
        If Range("B" & LRow).Value = "" Or UCase(Range("B" & LRow)) = "TAPE" Then
            Rows(LRow).EntireRow.Delete
        End If
    Next LRow


Application.ScreenUpdating = True
End Sub

Adapted from the below link



http://www.mrexcel.com/forum/excel-...applications-delete-row-based-cell-value.html

 

[tmd63]

Thank you.

I had to adapt it slightly as it did not delete the rows correctly.


If Range("B" & LRow).Value = "" Or UCase(Range("B" & LRow)) = "TAPE" Then
deleted blank rows also!

If Range("B" & LRow).Value = "Tape" Or UCase(Range("B" & LRow)) = "Tape" Then

This is my new code.
</pre>

 

[dispelthemyth]

Then you will only need

If UCase(Range("B" & LRow)) = "TAPE" Then

Thank you.

I had to adapt it slightly as it did not delete the rows correctly.


If Range("B" & LRow).Value = "" Or UCase(Range("B" & LRow)) = "TAPE" Then
deleted blank rows also!

If Range("B" & LRow).Value = "Tape" Or UCase(Range("B" & LRow)) = "Tape" Then

This is my new code.

 

[hiker95]

tmd63,

Below is a solution without looping.

Sample raw data:

Excel 2007
	B
1	Title B
2	Tape
3	tape
4	4
5	Tape
6	6
7	TAPE
8	8
9	tape
10	tape
11	10
12
Sheet1

After the macro:

Excel 2007
	B
1	Title B
2	4
3	6
4	8
5	10
6
7
8
9
10
11
12
Sheet1

Sample raw data:

Excel 2007
	B
1	Title B
2	Tape
3	tape
4	TAPE
5	Tape
6	TAPE
7	TAPE
8	tape
9	tape
10	tape
11	TAPE
12
Sheet1

After the macro:

Excel 2007
	B
1	Title B
2
3
4
5
6
7
8
9
10
11
12
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Option Explicit
Option Compare Text
Sub DeleteColB_rows_tape()
' hiker95, 02/21/2014, ME759272
Dim lr As Long
Application.ScreenUpdating = False
lr = Cells(Rows.Count, "B").End(xlUp).Row
Range("B2:B" & lr) = Evaluate("IF(B2:B" & lr & "<>""tape"",B2:B" & lr & ","""")")
On Error Resume Next
Range("B1:B" & lr).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
On Error GoTo 0
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the DeleteColB_rows_tape macro.

 

[Rick Rothstein]

If the values in Column B are all constants (that is, Column B has no formulas in it), then here is another non-looping macro for you to try...

Sub DeleteRowsWithTapeInColumnB()
  Columns("B").Replace "Tape", "#N/A", xlWhole, , False
  On Error Resume Next
  Columns("B").SpecialCells(xlConstants, xlErrors).EntireRow.Delete
  On Error GoTo 0
End Sub

 

[hiker95]

Rick Rothstein,

Nice - another for my archives - thanks.

You really need to write that book I mentioned, below in replies #9, and #10.

http://www.mrexcel.com/forum/excel-...ue-insert-row-copy-paste-based-condition.html