Dynamically locating value; selecting that row and everything above (or below) and deleting/copying

[Steven Rosenberg]

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!

 

[Ron de Bruin]

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

 

[Ken Wright]

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

 

[Ken Wright]

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

 

[Robert Rosenberg]

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.

 

[Ron de Bruin]

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

 

[Ken Wright]

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

 

[Ken Wright]

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

 

[Ron de Bruin]

I'm curious now

We all<g>

 

[Robert Rosenberg]

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

I have no idea. <bg>

 

[Ken Wright]

rotflmao

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

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

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

 

[Ron de Bruin]

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

 

[Steven Rosenberg]

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!

 

[Ron de Bruin]

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

 

[Ken Wright]

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