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

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



--
Regards
Frank Kabel
Frankfurt, Germany

Steve wrote:
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

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
-----Original Message-----
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



--
Regards
Frank Kabel
Frankfurt, Germany

Steve wrote:
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
.

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


--
Regards
Frank Kabel
Frankfurt, Germany

wrote:
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
-----Original Message-----
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



--
Regards
Frank Kabel
Frankfurt, Germany

Steve wrote:
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
.

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
-----Original Message-----
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


--
Regards
Frank Kabel
Frankfurt, Germany

wrote:
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
-----Original Message-----
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



--
Regards
Frank Kabel
Frankfurt, Germany

Steve wrote:
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
.

.

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


--
Regards
Frank Kabel
Frankfurt, Germany

Steve wrote:
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
-----Original Message-----
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


--
Regards
Frank Kabel
Frankfurt, Germany

wrote:
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
-----Original Message-----
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



--
Regards
Frank Kabel
Frankfurt, Germany

Steve wrote:
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
.

.

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


"Steve" wrote in message
...
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

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

"Steve" wrote in message
...
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

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

"Steve" wrote in
message
...
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



.

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
-----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

"Steve" wrote in
message
...
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



.

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

--
Regards
Frank Kabel
Frankfurt, Germany

wrote:
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
-----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

"Steve" wrote in message
...
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



.