View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.programming
steve steve is offline
external usenet poster
 
Posts: 576
Default Delete Rows above keyword

Brian,

Borrowed from Tom Olgivy.
Check the notes.
Test the code (code not tested)

Dim rng As Range
Dim rng1 As Range
Dim sStr As String
Set rng1 = Nothing
Set rng = Columns(1)
sStr = "ABCD"
' or use: sStr = inputbox("What to find")
Set rng1 = rng.Find(What:=sStr) ' change to suit your needs
If Not rng1 Is Nothing Then
Range(Rows(1), Rows(rng1.Row - 1)).Delete
Else
MsgBox "Not found"
End If

--
sb
"Brian" wrote in message
...
I would like to use vba to delete all rows that are above
a certain keyword. Right now I have a macro that will
separate a customers PO by location but when it does, some
of the sheets have many blank rows at the top. The reason
that I would like it to find a keyword is because on some
of them, there is useless information within the blank
area. For example on one of the sheets, there maybe 10
blank rows, then in cell A1 there may be the word 'Car'
and then 10 more blank rows before cell A1 has the
customer information. I would like to be able to delete
all of the rows above the customer information. Hope that
makes sense. tia