delete rows-macro

[TUNGANA KURMA RAJU]

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

[Norman Jones]

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" <TUNGANAKURMARAJU@discussions.microsoft.com> wrote in
message news:F3711B82-9C1E-4B7F-BCEE-FF9F2BA3488F@microsoft.com...
>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
>

[TUNGANA KURMA RAJU]

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" <TUNGANAKURMARAJU@discussions.microsoft.com> wrote in
> message news:F3711B82-9C1E-4B7F-BCEE-FF9F2BA3488F@microsoft.com...
> >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
> >
>
>
>

[Norman Jones]

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" <TUNGANAKURMARAJU@discussions.microsoft.com> wrote in
message news:03ACE518-DEF8-4A0E-862F-1933497B8A0F@microsoft.com...
> 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" <TUNGANAKURMARAJU@discussions.microsoft.com> wrote
>> in
>> message news:F3711B82-9C1E-4B7F-BCEE-FF9F2BA3488F@microsoft.com...
>> >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
>> >
>>
>>
>>

[TUNGANA KURMA RAJU]

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" <TUNGANAKURMARAJU@discussions.microsoft.com> wrote in
> message news:03ACE518-DEF8-4A0E-862F-1933497B8A0F@microsoft.com...
> > 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" <TUNGANAKURMARAJU@discussions.microsoft.com> wrote
> >> in
> >> message news:F3711B82-9C1E-4B7F-BCEE-FF9F2BA3488F@microsoft.com...
> >> >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
> >> >
> >>
> >>
> >>
>
>
>

[Norman Jones]

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" <TUNGANAKURMARAJU@discussions.microsoft.com> wrote in
message news:9D4331B9-8BC0-4ACC-845C-CFA9FCB3E179@microsoft.com...
> Thanks,Mr.Jones,
> What a woderful thing "vba",I would like to learn.Yours macro working
> great.