Deleting rows after a specific Value
 

Anyones help would be rgeatly appreciated, it shouldnt be har
but...here i am!

I need a macro to find a certain value in a worksheet(the same valu
everytime) and then delete the row that contains that value and al
rows beneath that row. There will only ever be one occurence of th
specific value in the worksheet.

The position of the value may change every time i use it e.g it may b
on row 250 one time and on 300 the next etc

So i need a macro to find what row the value is on and delete it an
all rows below it, if anyone could help that would be brilliant!

--
Steel Monke
-----------------------------------------------------------------------
Steel Monkey's Profile: http://www.excelforum.com/member.php...fo&userid=2905
View this thread: http://www.excelforum.com/showthread.php?threadid=54768

Deleting rows after a specific Value
 

Hi Steel Monkey,

Try:

'=============
Public Sub Tester()
Dim WB As Workbook
Dim SH As Worksheet
Dim rng As Range
Dim i As Long
Const sStr As String = "ABCDE" '<<==== CHANGE

Set WB = Workbooks("YourBook.xls") '<<==== CHANGE
Set SH = WB.Sheets("Sheet3") '<<==== CHANGE

With SH
Set rng = .Cells.Find(What:="ABCDE", _
After:=.Range("A1"), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)

If Not rng Is Nothing Then
With .UsedRange
rng.Resize(.Rows.Count - .Row + 1). _
EntireRow.Delete
End With
End If
End With

End Sub
'<<=============


---
Regards,
Norman


"Steel Monkey"
wrote in message
news:Steel.Monkey.28qz0y_1149205501.5464@excelforu m-nospam.com...

Anyones help would be rgeatly appreciated, it shouldnt be hard
but...here i am!

I need a macro to find a certain value in a worksheet(the same value
everytime) and then delete the row that contains that value and all
rows beneath that row. There will only ever be one occurence of the
specific value in the worksheet.

The position of the value may change every time i use it e.g it may be
on row 250 one time and on 300 the next etc

So i need a macro to find what row the value is on and delete it and
all rows below it, if anyone could help that would be brilliant!!


--
Steel Monkey
------------------------------------------------------------------------
Steel Monkey's Profile:
http://www.excelforum.com/member.php...o&userid=29051
View this thread: http://www.excelforum.com/showthread...hreadid=547682

Deleting rows after a specific Value
 

Worked like a charm, thank you very much!


--
Steel Monkey
------------------------------------------------------------------------
Steel Monkey's Profile: http://www.excelforum.com/member.php...o&userid=29051
View this thread: http://www.excelforum.com/showthread...hreadid=547682