Macro to delete specific rows
 

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

Macro to delete specific rows
 

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

Macro to delete specific rows
 

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

Macro to delete specific rows
 

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

Macro to delete specific rows
 

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



.

Macro to delete specific rows
 

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



.

Macro to delete specific rows
 

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



.

Macro to delete specific rows
 

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
.

Macro to delete specific rows
 

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
.

Macro to delete specific rows
 

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
.

.

Macro to delete specific rows
 

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
.

.

Macro to delete specific rows
 

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

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

.

.

Macro to delete specific rows
 

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

Frank Kabel Wrote:
Hi Steve
so you want to delete the row if either of these values is in colum
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
.



--
as
-----------------------------------------------------------------------
asf's Profile: http://www.excelforum.com/member.php...fo&userid=1493
View this thread: http://www.excelforum.com/showthread.php?threadid=19765