I am looking for a macro,that checks a value in a w/sheet range B:B ,if
found,delete the row.
Example:
col a-----------------------col b-----------------------colc
john-----------------------manager------------------$500
lucy------------------------supervisor-----------------$250
cathy----------------------manager-------------------$650
Ibrahim-------------------supervisor------------------$325
david----------------------worker----------------------$200
macro should check,suppose a value in b:b 'manager' ,if I run macro,the list
will be
Col a----------------------col b-------------------------col c
lucy------------------------supervisor-----------------$250
Ibrahim-------------------supervisor------------------$325
david----------------------worker----------------------$200

Hi Tungana,

Try:
'=============
Public Sub Tester()
Dim WB As Workbook
Dim SH As Worksheet
Dim rng As Range
Dim rcell As Range
Dim delRng As Range
Dim LRow As Long
Dim CalcMode As Long
Const sStr As String = "manager" '<<===== CHANGE

Set WB = ActiveWorkbook '<<===== CHANGE
Set SH = WB.Sheets("Sheet1") '<<===== CHANGE

LRow = Cells(Rows.Count, "B").End(xlUp).Row

Set rng = SH.Range("B1").Resize(LRow)

With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

For Each rcell In rng.Cells
If LCase(rcell.Value) = LCase(sStr) Then
If delRng Is Nothing Then
Set delRng = rcell
Else
Set delRng = Union(rcell, delRng)
End If
End If
Next rcell

If Not delRng Is Nothing Then
delRng.EntireRow.Delete
Else
'nothing found, do nothing
End If

With Application
.Calculation = CalcMode
.ScreenUpdating = True
End With

End Sub
'<<=============

---
Regards,
Norman


"TUNGANA KURMA RAJU" wrote in
message ...
I am looking for a macro,that checks a value in a w/sheet range B:B ,if
found,delete the row.
Example:
col a-----------------------col b-----------------------colc
john-----------------------manager------------------$500
lucy------------------------supervisor-----------------$250
cathy----------------------manager-------------------$650
Ibrahim-------------------supervisor------------------$325
david----------------------worker----------------------$200
macro should check,suppose a value in b:b 'manager' ,if I run macro,the
list
will be
Col a----------------------col b-------------------------col c
lucy------------------------supervisor-----------------$250
Ibrahim-------------------supervisor------------------$325
david----------------------worker----------------------$200

Hi ! Many many thanks,its working well.I am a novice to VBA.At three places
you have mentioned '<<=======CHANGE, what it is ?.Please inform
me.secondly,It is just my curiosity,can you change this code with a input
box,when prompted user will enter a value of his choice.In this case lookup
value is "manager", which is fixed.If user enters his choice value
say,"worker" or "supervisor" accordingly this macro will function.Thanks

"Norman Jones" wrote:

Hi Tungana,

Try:
'=============
Public Sub Tester()
Dim WB As Workbook
Dim SH As Worksheet
Dim rng As Range
Dim rcell As Range
Dim delRng As Range
Dim LRow As Long
Dim CalcMode As Long
Const sStr As String = "manager" '<<===== CHANGE

Set WB = ActiveWorkbook '<<===== CHANGE
Set SH = WB.Sheets("Sheet1") '<<===== CHANGE

LRow = Cells(Rows.Count, "B").End(xlUp).Row

Set rng = SH.Range("B1").Resize(LRow)

With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

For Each rcell In rng.Cells
If LCase(rcell.Value) = LCase(sStr) Then
If delRng Is Nothing Then
Set delRng = rcell
Else
Set delRng = Union(rcell, delRng)
End If
End If
Next rcell

If Not delRng Is Nothing Then
delRng.EntireRow.Delete
Else
'nothing found, do nothing
End If

With Application
.Calculation = CalcMode
.ScreenUpdating = True
End With

End Sub
'<<=============

---
Regards,
Norman


"TUNGANA KURMA RAJU" wrote in
message ...
I am looking for a macro,that checks a value in a w/sheet range B:B ,if
found,delete the row.
Example:
col a-----------------------col b-----------------------colc
john-----------------------manager------------------$500
lucy------------------------supervisor-----------------$250
cathy----------------------manager-------------------$650
Ibrahim-------------------supervisor------------------$325
david----------------------worker----------------------$200
macro should check,suppose a value in b:b 'manager' ,if I run macro,the
list
will be
Col a----------------------col b-------------------------col c
lucy------------------------supervisor-----------------$250
Ibrahim-------------------supervisor------------------$325
david----------------------worker----------------------$200

Hi Tungana,

you have mentioned '<<=======CHANGE, what it is ?.Please inform
me

Const sStr As String = "manager" '<<===== CHANGE

Set WB = ActiveWorkbook '<<===== CHANGE
Set SH = WB.Sheets("Sheet1") '<<===== CHANGE

If your strin is "manager", your workbook is the active workbook and the
sheet of interest is named "Sheet1", then nothing needs to be changed.

If, however, (say) your sheet were named "Tungana" and the workbook were
named "ABC.xls" and this was not the activeworkbook, then you might amend
this code snippet to read:

Const sStr As String = "manager"

Set WB =Workbooks("ABC.xls")
Set SH = WB.Sheets("Tungana")

secondly,It is just my curiosity,can you change this code with a input
box,when prompted user will enter a value of his choice.In this case
lookup value is "manager", which is fixed.If user enters his choice
value say,"worker" or "supervisor" accordingly this macro will
function.Thanks

Try:
'=============
Public Sub TesterZ()
Dim WB As Workbook
Dim SH As Worksheet
Dim rng As Range
Dim rcell As Range
Dim delRng As Range
Dim LRow As Long
Dim CalcMode As Long
Dim sStr As String

Set WB = ActiveWorkbook
Set SH = WB.Sheets("Sheet1")

sStr = InputBox("Please enter the search string")

LRow = Cells(Rows.Count, "B").End(xlUp).Row

Set rng = SH.Range("B1").Resize(LRow)

With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

For Each rcell In rng.Cells
If LCase(rcell.Value) = LCase(sStr) Then
If delRng Is Nothing Then
Set delRng = rcell
Else
Set delRng = Union(rcell, delRng)
End If
End If
Next rcell

If Not delRng Is Nothing Then
delRng.EntireRow.Delete
Else
'nothing found, do nothing
End If

With Application
.Calculation = CalcMode
.ScreenUpdating = True
End With

End Sub
'<<=============

---
Regards,
Norman


"TUNGANA KURMA RAJU" wrote in
message ...
Hi ! Many many thanks,its working well.I am a novice to VBA.At three
places
you have mentioned '<<=======CHANGE, what it is ?.Please inform
me.secondly,It is just my curiosity,can you change this code with a input
box,when prompted user will enter a value of his choice.In this case
lookup
value is "manager", which is fixed.If user enters his choice value
say,"worker" or "supervisor" accordingly this macro will function.Thanks

"Norman Jones" wrote:

Hi Tungana,

Try:
'=============
Public Sub Tester()
Dim WB As Workbook
Dim SH As Worksheet
Dim rng As Range
Dim rcell As Range
Dim delRng As Range
Dim LRow As Long
Dim CalcMode As Long

LRow = Cells(Rows.Count, "B").End(xlUp).Row

Set rng = SH.Range("B1").Resize(LRow)

With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

For Each rcell In rng.Cells
If LCase(rcell.Value) = LCase(sStr) Then
If delRng Is Nothing Then
Set delRng = rcell
Else
Set delRng = Union(rcell, delRng)
End If
End If
Next rcell

If Not delRng Is Nothing Then
delRng.EntireRow.Delete
Else
'nothing found, do nothing
End If

With Application
.Calculation = CalcMode
.ScreenUpdating = True
End With

End Sub
'<<=============

---
Regards,
Norman


"TUNGANA KURMA RAJU" wrote
in
message ...
I am looking for a macro,that checks a value in a w/sheet range B:B ,if
found,delete the row.
Example:
col a-----------------------col b-----------------------colc
john-----------------------manager------------------$500
lucy------------------------supervisor-----------------$250
cathy----------------------manager-------------------$650
Ibrahim-------------------supervisor------------------$325
david----------------------worker----------------------$200
macro should check,suppose a value in b:b 'manager' ,if I run macro,the
list
will be
Col a----------------------col b-------------------------col c
lucy------------------------supervisor-----------------$250
Ibrahim-------------------supervisor------------------$325
david----------------------worker----------------------$200

Thanks,Mr.Jones,
What a woderful thing "vba",I would like to learn.Yours macro working great.

"Norman Jones" wrote:

Hi Tungana,

you have mentioned '<<=======CHANGE, what it is ?.Please inform
me

Const sStr As String = "manager" '<<===== CHANGE

Set WB = ActiveWorkbook '<<===== CHANGE
Set SH = WB.Sheets("Sheet1") '<<===== CHANGE

If your strin is "manager", your workbook is the active workbook and the
sheet of interest is named "Sheet1", then nothing needs to be changed.

If, however, (say) your sheet were named "Tungana" and the workbook were
named "ABC.xls" and this was not the activeworkbook, then you might amend
this code snippet to read:

Const sStr As String = "manager"

Set WB =Workbooks("ABC.xls")
Set SH = WB.Sheets("Tungana")

secondly,It is just my curiosity,can you change this code with a input
box,when prompted user will enter a value of his choice.In this case
lookup value is "manager", which is fixed.If user enters his choice
value say,"worker" or "supervisor" accordingly this macro will
function.Thanks

Try:
'=============
Public Sub TesterZ()
Dim WB As Workbook
Dim SH As Worksheet
Dim rng As Range
Dim rcell As Range
Dim delRng As Range
Dim LRow As Long
Dim CalcMode As Long
Dim sStr As String

Set WB = ActiveWorkbook
Set SH = WB.Sheets("Sheet1")

sStr = InputBox("Please enter the search string")

LRow = Cells(Rows.Count, "B").End(xlUp).Row

Set rng = SH.Range("B1").Resize(LRow)

With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

For Each rcell In rng.Cells
If LCase(rcell.Value) = LCase(sStr) Then
If delRng Is Nothing Then
Set delRng = rcell
Else
Set delRng = Union(rcell, delRng)
End If
End If
Next rcell

If Not delRng Is Nothing Then
delRng.EntireRow.Delete
Else
'nothing found, do nothing
End If

With Application
.Calculation = CalcMode
.ScreenUpdating = True
End With

End Sub
'<<=============

---
Regards,
Norman


"TUNGANA KURMA RAJU" wrote in
message ...
Hi ! Many many thanks,its working well.I am a novice to VBA.At three
places
you have mentioned '<<=======CHANGE, what it is ?.Please inform
me.secondly,It is just my curiosity,can you change this code with a input
box,when prompted user will enter a value of his choice.In this case
lookup
value is "manager", which is fixed.If user enters his choice value
say,"worker" or "supervisor" accordingly this macro will function.Thanks

"Norman Jones" wrote:

Hi Tungana,

Try:
'=============
Public Sub Tester()
Dim WB As Workbook
Dim SH As Worksheet
Dim rng As Range
Dim rcell As Range
Dim delRng As Range
Dim LRow As Long
Dim CalcMode As Long

LRow = Cells(Rows.Count, "B").End(xlUp).Row

Set rng = SH.Range("B1").Resize(LRow)

With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

For Each rcell In rng.Cells
If LCase(rcell.Value) = LCase(sStr) Then
If delRng Is Nothing Then
Set delRng = rcell
Else
Set delRng = Union(rcell, delRng)
End If
End If
Next rcell

If Not delRng Is Nothing Then
delRng.EntireRow.Delete
Else
'nothing found, do nothing
End If

With Application
.Calculation = CalcMode
.ScreenUpdating = True
End With

End Sub
'<<=============

---
Regards,
Norman


"TUNGANA KURMA RAJU" wrote
in
message ...
I am looking for a macro,that checks a value in a w/sheet range B:B ,if
found,delete the row.
Example:
col a-----------------------col b-----------------------colc
john-----------------------manager------------------$500
lucy------------------------supervisor-----------------$250
cathy----------------------manager-------------------$650
Ibrahim-------------------supervisor------------------$325
david----------------------worker----------------------$200
macro should check,suppose a value in b:b 'manager' ,if I run macro,the
list
will be
Col a----------------------col b-------------------------col c
lucy------------------------supervisor-----------------$250
Ibrahim-------------------supervisor------------------$325
david----------------------worker----------------------$200

Hi Tungana,

What a woderful thing "vba",I would like to learn

You might wish to visit David McRitichie's tutorials page at:

http://www.mvps.org/dmcritchie/excel....htm#tutorials

The VBA material is towards the end of that section.

I would also suggest that you purchase a good book; John Walkenbach's books
receive universal acclaim:

http://www.j-walk.com/ss/books/index.htm

See also Debra Dalgleish's listing at:

http://www.contextures.com/xlbooks.html


---
Regards,
Norman



"TUNGANA KURMA RAJU" wrote in
message ...
Thanks,Mr.Jones,
What a woderful thing "vba",I would like to learn.Yours macro working
great.