PDA

View Full Version : Delete all rows after....



Lande
11-16-2007, 02:15 PM
I am trying to delete all rows after a certain cell has a certain value. For example, in Column A, I have the following 10 rows:
1
2
3
4
Last Row
Not Needed
7
8
9
10

I am looking to delete all rows (or clear the contents) starting with the one that has the "Not Needed" in it, so I only want to keep rows 1 through "Last Row".

Thank you!

JimmyTheHand
11-16-2007, 03:05 PM
Try this

Sub test()
Dim rng As Range
Set rng = Range("A:A").Find("Not Needed", , xlValues, xlPart, , , False)
If rng Is Nothing Then
MsgBox "String 'Not Needed' was not found."
Exit Sub
Else
Set rng = Range(rng, rng.End(xlDown))
rng.EntireRow.Delete
End If
End Sub

Simon Lloyd
11-17-2007, 11:17 AM
Or try this:

Sub Macro2()
Dim FndCell As String
Dim Rng As String
FndCell = Cells.Find(What:="Not Needed", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Address
Rng = Range("A65536").End(xlUp).Address
Range(Rng & ":" & FndCell).EntireRow.Delete (xlUp)

End Sub

AlexAlexon
11-17-2007, 02:04 PM
Sorry, this is not my thread but can you explain Simon this part of your code "Range(Rng & ":" & FndCell)."

Simon Lloyd
11-23-2007, 03:57 AM
Certainly!, this
Range(Rng & ":" & FndCell)is the same as writing
Range("A1:B20")where A1 would be the Rng i set in this statement
Rng = Range("A65536").End(xlUp).Address i Declared In Memory the name Rng as a String because i used the suffix .Address at the end, FndCell is the name i had given to the results of the FIND function which is also a string, the : is used as in a normal range statement .

Hope this helps.