Macro to delete specific rows

[Steve]

I am looking for a macro to delete an entire row, when
there is a specific occurance in a cell. For instance, if
cell b3 has the letters WX in it. I want to delete all of
row b.

I appreciate all your time and effort.
Steve

 

[Frank Kabel]

Hi
try the following

Public Sub DeleteRows()

Dim R As Long
Dim C As Range
Dim Rng As Range

On Error GoTo EndMacro
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

If Selection.Rows.Count > 1 Then
Set Rng = Selection
Else
Set Rng = ActiveSheet.UsedRange.Rows
End If
For R = Rng.Rows.Count To 1 Step -1
If cells(r,"B").value = "WX" Then
Rng.Rows(R).EntireRow.Delete
End If
Next R

EndMacro:

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub

 

[Chip Pearson]

Steve,
Try something like

Dim RowNdx As Long
Dim LastRow As Long
LastRow = Cells(Rows.Count, "B").End(xlUp).Row
For RowNdx = LastRow To 1 Step -1
If Cells(RowNdx, "B").Value = "WX" Then
Rows(RowNdx).Delete
End If
Next RowNdx


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Shatin]

Do you mean you want to delete row 3 or column B?

Try something like:

Sub deleteRow()

If InStr(Range("B3"), "WX") Then
Rows(3).Delete
End If

End Sub

 

[Guest]

HEY...MY BAD.... I MEANT I WANT TO DELETE ROW 3.

-----Original Message-----
Do you mean you want to delete row 3 or column B?

Try something like:

Sub deleteRow()

If InStr(Range("B3"), "WX") Then
Rows(3).Delete
End If

End Sub




.

 

[Guest]

the problem i am having is the spreadsheet will have
several occurances, not in any order. how do i set a range
to delete any row with that occurance?
i appreciate your patience. I am still learning macros and
VBA.
Thanks,
Steve

 

[Frank Kabel]

Hi
see Chip's or my response to your thred. They both will work on the
used range of your worksheet

 

[Guest]

First, let me apologize for my blatant ignorance. i tried
copying and pasting it into module 4 after another macro i
have in. Now i can't figure out how to either assign your
macro to a button, or simply run it. when i go to the
macro menu, it is not listed.

thanks again for your time.
steve

 

[Frank Kabel]

Hi
should work if you pasted this macro in a module of your current
workbook (it should appear in the macro menu)
You may have a look at the following site for more information how to
use/install macros

http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[Steve]

All right, i finally got it too work. i have one final
question....How do I add multiple values. so that along w/
wx, it will also look for pax, cus, atc.

once again I thank you all for your help.
Steve

 

[Frank Kabel]

Hi Steve
so you want to delete the row if either of these values is in column B.
Try

Public Sub DeleteRows()

Dim R As Long
Dim C As Range
Dim Rng As Range

On Error GoTo EndMacro
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

If Selection.Rows.Count > 1 Then
Set Rng = Selection
Else
Set Rng = ActiveSheet.UsedRange.Rows
End If
For R = Rng.Rows.Count To 1 Step -1
If cells(r,"B").value = "WX" or _
cells(r,"B").value = "PAX" or _
cells(r,"B").value = "CUS" or _
Then
Rng.Rows(R).EntireRow.Delete
End If
Next R

 

[Guest]

Frank,
you just made me look good for the boss. I appreciate all
your time and effort.

Steve.

 

[asf]

Frank,

Your macro help is great. Say I want to do the same as steve but delet
any rows that contain "XXX" anywhere in the field, so XXX is never alon
but always adjacent to other text.

Thanks in advance!!