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

  • Thread starter Thread starter Steven Rosenberg
  • Start date Start date
S

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

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.
 
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
 
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 :-)
 
I can read the mind of a fellow Rosenberg better than any of you...

I have no idea. <bg>
 
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 said:
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
Click to expand...
<snip>>
 
For the fun said:
for a specific record and that's all he wants to keep
Click to expand...

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