How can I write a VBA macro which:

--will locate a specific value (a word) in column A of a
multi column worksheet; then

--select that cell's entire row, and all rows above (or
below) it; then

--delete the selected rows?

This non-programmer would appreciate any and all
help.

Steven

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Try this

It will look for "ron" in Column A
It will delete that row and all the rows above it

Sub test()
Dim Rng As Range
Set Rng = Range("A:A").Find(What:="ron", After:=Range("A" _
& Rows.Count), LookAt:=xlWhole)
If Not Rng Is Nothing Then Rows("1:" & Rng.Row).delete
End Sub


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)
www.rondebruin.nl



"Steven Rosenberg" wrote in message ...
How can I write a VBA macro which:

--will locate a specific value (a word) in column A of a
multi column worksheet; then

--select that cell's entire row, and all rows above (or
below) it; then

--delete the selected rows?

This non-programmer would appreciate any and all
help.

Steven

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

One way:-

Sub DelRows()

ans = InputBox("What string do you want to find and then delete all other
rows?")
Application.ScreenUpdating = False

LastRow = ActiveSheet.UsedRange.Row - 1 + _
ActiveSheet.UsedRange.Rows.Count

Set Rng = Range(Cells(1, "A"), Cells(LastRow, "A"))

With Rng
.AutoFilter
.AutoFilter Field:=1, Criteria1:="<" & ans
.SpecialCells(xlCellTypeVisible).EntireRow.Delete
End With
Application.ScreenUpdating = True

End Sub

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------



"Steven Rosenberg" wrote in message
...
How can I write a VBA macro which:

--will locate a specific value (a word) in column A of a
multi column worksheet; then

--select that cell's entire row, and all rows above (or
below) it; then

--delete the selected rows?

This non-programmer would appreciate any and all
help.

Steven

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.600 / Virus Database: 381 - Release Date: 28/02/2004

Hi Ron - Think you missed the caveat re 'below as well'

all rows above (or below) it;

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------



"Ron de Bruin" wrote in message
...
Try this

It will look for "ron" in Column A
It will delete that row and all the rows above it

Sub test()
Dim Rng As Range
Set Rng = Range("A:A").Find(What:="ron", After:=Range("A" _
& Rows.Count), LookAt:=xlWhole)
If Not Rng Is Nothing Then Rows("1:" & Rng.Row).delete
End Sub


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)
www.rondebruin.nl



"Steven Rosenberg" wrote in message
...
How can I write a VBA macro which:

--will locate a specific value (a word) in column A of a
multi column worksheet; then

--select that cell's entire row, and all rows above (or
below) it; then

--delete the selected rows?

This non-programmer would appreciate any and all
help.

Steven

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!




---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.600 / Virus Database: 381 - Release Date: 28/02/2004

Ken,

I think you may have missed the caveat re 'OR below' <g

I'm unsure whether the poster wants to have the choice of above OR below but
it didn't sound like both.

--
__________________
Robert Rosenberg
R-COR Consulting Services
Microsoft MVP - Excel


"Ken Wright" wrote in message
...
One way:-

Sub DelRows()

ans = InputBox("What string do you want to find and then delete all other
rows?")
Application.ScreenUpdating = False

LastRow = ActiveSheet.UsedRange.Row - 1 + _
ActiveSheet.UsedRange.Rows.Count

Set Rng = Range(Cells(1, "A"), Cells(LastRow, "A"))

With Rng
.AutoFilter
.AutoFilter Field:=1, Criteria1:="<" & ans
.SpecialCells(xlCellTypeVisible).EntireRow.Delete
End With
Application.ScreenUpdating = True

End Sub

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03

--------------------------------------------------------------------------
--
It's easier to beg forgiveness than ask permission :-)
--------------------------------------------------------------------------
--



"Steven Rosenberg" wrote in message
...
How can I write a VBA macro which:

--will locate a specific value (a word) in column A of a
multi column worksheet; then

--select that cell's entire row, and all rows above (or
below) it; then

--delete the selected rows?

This non-programmer would appreciate any and all
help.

Steven

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.600 / Virus Database: 381 - Release Date: 28/02/2004

Hi Ken

I give one<g

Try this for below

Sub test2()
Dim Rng As Range
Set Rng = Range("A:A").Find(What:="ron", After:=Range("A" _
& Rows.Count), LookAt:=xlWhole)
If Not Rng Is Nothing Then Rows(Rng.Row & ":" & Rows.Count).delete
End Sub


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)
www.rondebruin.nl



"Ken Wright" wrote in message ...
Hi Ron - Think you missed the caveat re 'below as well'

all rows above (or below) it;

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------



"Ron de Bruin" wrote in message
...
Try this

It will look for "ron" in Column A
It will delete that row and all the rows above it

Sub test()
Dim Rng As Range
Set Rng = Range("A:A").Find(What:="ron", After:=Range("A" _
& Rows.Count), LookAt:=xlWhole)
If Not Rng Is Nothing Then Rows("1:" & Rng.Row).delete
End Sub


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)
www.rondebruin.nl



"Steven Rosenberg" wrote in message
...
How can I write a VBA macro which:

--will locate a specific value (a word) in column A of a
multi column worksheet; then

--select that cell's entire row, and all rows above (or
below) it; then

--delete the selected rows?

This non-programmer would appreciate any and all
help.

Steven

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!




---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.600 / Virus Database: 381 - Release Date: 28/02/2004

LOL - Guess it's open to interpretation, but I was assuming that he is looking
for a specific record and that's all he wants to keep. Over to the OP for
clarification I guess, coz I'm curious now :-)

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------



"Robert Rosenberg" wrote in message
...
Ken,

I think you may have missed the caveat re 'OR below' <g

I'm unsure whether the poster wants to have the choice of above OR below but
it didn't sound like both.

--
__________________
Robert Rosenberg
R-COR Consulting Services
Microsoft MVP - Excel


"Ken Wright" wrote in message
...
One way:-

Sub DelRows()

ans = InputBox("What string do you want to find and then delete all other
rows?")
Application.ScreenUpdating = False

LastRow = ActiveSheet.UsedRange.Row - 1 + _
ActiveSheet.UsedRange.Rows.Count

Set Rng = Range(Cells(1, "A"), Cells(LastRow, "A"))

With Rng
.AutoFilter
.AutoFilter Field:=1, Criteria1:="<" & ans
.SpecialCells(xlCellTypeVisible).EntireRow.Delete
End With
Application.ScreenUpdating = True

End Sub

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03

--------------------------------------------------------------------------
--
It's easier to beg forgiveness than ask permission :-)
--------------------------------------------------------------------------
--



"Steven Rosenberg" wrote in message
...
How can I write a VBA macro which:

--will locate a specific value (a word) in column A of a
multi column worksheet; then

--select that cell's entire row, and all rows above (or
below) it; then

--delete the selected rows?

This non-programmer would appreciate any and all
help.

Steven

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.600 / Virus Database: 381 - Release Date: 28/02/2004






---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.600 / Virus Database: 381 - Release Date: 28/02/2004

LOL - Now Ron's got me wondering what it really is the Op wants :-)

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------



"Ron de Bruin" wrote in message
...
Hi Ken

I give one<g

Try this for below

Sub test2()
Dim Rng As Range
Set Rng = Range("A:A").Find(What:="ron", After:=Range("A" _
& Rows.Count), LookAt:=xlWhole)
If Not Rng Is Nothing Then Rows(Rng.Row & ":" & Rows.Count).delete
End Sub


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)
www.rondebruin.nl



"Ken Wright" wrote in message
...
Hi Ron - Think you missed the caveat re 'below as well'

all rows above (or below) it;

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------



"Ron de Bruin" wrote in message
...
Try this

It will look for "ron" in Column A
It will delete that row and all the rows above it

Sub test()
Dim Rng As Range
Set Rng = Range("A:A").Find(What:="ron", After:=Range("A" _
& Rows.Count), LookAt:=xlWhole)
If Not Rng Is Nothing Then Rows("1:" & Rng.Row).delete
End Sub


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)
www.rondebruin.nl



"Steven Rosenberg" wrote in message
...
How can I write a VBA macro which:

--will locate a specific value (a word) in column A of a
multi column worksheet; then

--select that cell's entire row, and all rows above (or
below) it; then

--delete the selected rows?

This non-programmer would appreciate any and all
help.

Steven

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!




---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.600 / Virus Database: 381 - Release Date: 28/02/2004






---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.600 / Virus Database: 381 - Release Date: 28/02/2004

I'm curious now

We all<g

--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)
www.rondebruin.nl



"Ken Wright" wrote in message ...
LOL - Guess it's open to interpretation, but I was assuming that he is looking
for a specific record and that's all he wants to keep. Over to the OP for
clarification I guess, coz I'm curious now :-)

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------



"Robert Rosenberg" wrote in message
...
Ken,

I think you may have missed the caveat re 'OR below' <g

I'm unsure whether the poster wants to have the choice of above OR below but
it didn't sound like both.

--
__________________
Robert Rosenberg
R-COR Consulting Services
Microsoft MVP - Excel


"Ken Wright" wrote in message
...
One way:-

Sub DelRows()

ans = InputBox("What string do you want to find and then delete all other
rows?")
Application.ScreenUpdating = False

LastRow = ActiveSheet.UsedRange.Row - 1 + _
ActiveSheet.UsedRange.Rows.Count

Set Rng = Range(Cells(1, "A"), Cells(LastRow, "A"))

With Rng
.AutoFilter
.AutoFilter Field:=1, Criteria1:="<" & ans
.SpecialCells(xlCellTypeVisible).EntireRow.Delete
End With
Application.ScreenUpdating = True

End Sub

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03

--------------------------------------------------------------------------
--
It's easier to beg forgiveness than ask permission :-)
--------------------------------------------------------------------------
--



"Steven Rosenberg" wrote in message
...
How can I write a VBA macro which:

--will locate a specific value (a word) in column A of a
multi column worksheet; then

--select that cell's entire row, and all rows above (or
below) it; then

--delete the selected rows?

This non-programmer would appreciate any and all
help.

Steven

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.600 / Virus Database: 381 - Release Date: 28/02/2004






---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.600 / Virus Database: 381 - Release Date: 28/02/2004

I can read the mind of a fellow Rosenberg better than any of you...

I have no idea. <bg
--
__________________
Robert Rosenberg
R-COR Consulting Services
Microsoft MVP - Excel


"Ken Wright" wrote in message
...
LOL - Guess it's open to interpretation, but I was assuming that he is
looking
for a specific record and that's all he wants to keep. Over to the OP for
clarification I guess, coz I'm curious now :-)

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03

--------------------------------------------------------------------------
--
It's easier to beg forgiveness than ask permission :-)
--------------------------------------------------------------------------
--



"Robert Rosenberg" wrote in message
...
Ken,

I think you may have missed the caveat re 'OR below' <g

I'm unsure whether the poster wants to have the choice of above OR below
but
it didn't sound like both.

--
__________________
Robert Rosenberg
R-COR Consulting Services
Microsoft MVP - Excel


"Ken Wright" wrote in message
...
One way:-

Sub DelRows()

ans = InputBox("What string do you want to find and then delete all
other
rows?")
Application.ScreenUpdating = False

LastRow = ActiveSheet.UsedRange.Row - 1 + _
ActiveSheet.UsedRange.Rows.Count

Set Rng = Range(Cells(1, "A"), Cells(LastRow, "A"))

With Rng
.AutoFilter
.AutoFilter Field:=1, Criteria1:="<" & ans
.SpecialCells(xlCellTypeVisible).EntireRow.Delete
End With
Application.ScreenUpdating = True

End Sub

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03


--------------------------------------------------------------------------
--
It's easier to beg forgiveness than ask permission :-)

--------------------------------------------------------------------------
--



"Steven Rosenberg" wrote in message
...
How can I write a VBA macro which:

--will locate a specific value (a word) in column A of a
multi column worksheet; then

--select that cell's entire row, and all rows above (or
below) it; then

--delete the selected rows?

This non-programmer would appreciate any and all
help.

Steven

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.600 / Virus Database: 381 - Release Date: 28/02/2004






---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.600 / Virus Database: 381 - Release Date: 28/02/2004

rotflmao :-)

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------



"Robert Rosenberg" wrote in message
...
I can read the mind of a fellow Rosenberg better than any of you...

I have no idea. <bg
--
__________________
Robert Rosenberg
R-COR Consulting Services
Microsoft MVP - Excel

<snip


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.600 / Virus Database: 381 - Release Date: 28/02/2004

For the fun<g

for a specific record and that's all he wants to keep

Dim Rng As Range
Set Rng = Range("A:A").Find(What:="ron", After:=Range("A" _
& Rows.Count), LookAt:=xlWhole)
If Not Rng Is Nothing Then
Rows("1:" & Rng.Row - 1).delete
Rows(Rng.Row + 1 & ":" & Rows.Count).delete
End If
End Sub


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)
www.rondebruin.nl



"Ron de Bruin" wrote in message ...
I'm curious now

We all<g

--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)
www.rondebruin.nl



"Ken Wright" wrote in message ...
LOL - Guess it's open to interpretation, but I was assuming that he is looking
for a specific record and that's all he wants to keep. Over to the OP for
clarification I guess, coz I'm curious now :-)

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------



"Robert Rosenberg" wrote in message
...
Ken,

I think you may have missed the caveat re 'OR below' <g

I'm unsure whether the poster wants to have the choice of above OR below but
it didn't sound like both.

--
__________________
Robert Rosenberg
R-COR Consulting Services
Microsoft MVP - Excel


"Ken Wright" wrote in message
...
One way:-

Sub DelRows()

ans = InputBox("What string do you want to find and then delete all other
rows?")
Application.ScreenUpdating = False

LastRow = ActiveSheet.UsedRange.Row - 1 + _
ActiveSheet.UsedRange.Rows.Count

Set Rng = Range(Cells(1, "A"), Cells(LastRow, "A"))

With Rng
.AutoFilter
.AutoFilter Field:=1, Criteria1:="<" & ans
.SpecialCells(xlCellTypeVisible).EntireRow.Delete
End With
Application.ScreenUpdating = True

End Sub

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03

--------------------------------------------------------------------------
--
It's easier to beg forgiveness than ask permission :-)
--------------------------------------------------------------------------
--



"Steven Rosenberg" wrote in message
...
How can I write a VBA macro which:

--will locate a specific value (a word) in column A of a
multi column worksheet; then

--select that cell's entire row, and all rows above (or
below) it; then

--delete the selected rows?

This non-programmer would appreciate any and all
help.

Steven

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.600 / Virus Database: 381 - Release Date: 28/02/2004






---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.600 / Virus Database: 381 - Release Date: 28/02/2004

OK, what's wrong: this is what's in the procedure, but
when I run the macro, I get the error message "SUB OR
FUNCTION NOT DEFINED."

Public Sub FindNDeleteDateNoise()

Dim Rng As Range
***Set Rng = Range("A:A").Find(What:="01-Jan-09",
After:=Range("A" _
& Rows.Count), LookAt:=xlWhole)
If Not Rng Is Nothing Then Rows("1:" &
Rng.Row).Delete

End Sub

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Hi Steven

If you search for a Date instead of a text value then try this

Public Sub FindNDeleteDateNoise()
Dim Rng As Range
Set Rng = Range("A:A").Find(What:=DateValue("01-Jan-09"), _
After:=Range("A" & Rows.Count), LookAt:=xlWhole)
If Not Rng Is Nothing Then Rows("1:" & Rng.Row).Delete
End Sub


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)
www.rondebruin.nl



"Steven Rosenberg" wrote in message ...
OK, what's wrong: this is what's in the procedure, but
when I run the macro, I get the error message "SUB OR
FUNCTION NOT DEFINED."

Public Sub FindNDeleteDateNoise()

Dim Rng As Range
Set Rng = Range("A:A").Find(What:="01-Jan-09",
After:=Range("A" _
& Rows.Count), LookAt:=xlWhole)
If Not Rng Is Nothing Then Rows("1:" &
Rng.Row).Delete

End Sub

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

LOL - Come on Steve, put us out of our misery. Are you looking for a routine
that will:-

a) Delete all rows above the found record

b) Delete all rows below the found record

c) Delete all rows above AND below the found record

d) Give you the choice of a, b, c??

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------



"Steven Rosenberg" wrote in message
...
OK, what's wrong: this is what's in the procedure, but
when I run the macro, I get the error message "SUB OR
FUNCTION NOT DEFINED."

Public Sub FindNDeleteDateNoise()

Dim Rng As Range
Set Rng = Range("A:A").Find(What:="01-Jan-09",
After:=Range("A" _
& Rows.Count), LookAt:=xlWhole)
If Not Rng Is Nothing Then Rows("1:" &
Rng.Row).Delete

End Sub

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.600 / Virus Database: 381 - Release Date: 28/02/2004