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