Delete Rows above keyword
 

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

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

Delete Rows above keyword
 

Steve,

do you know if there is a way to modify this so that if my
keyword happens to be in cell A1 the macro will not error
out?



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




.

Delete Rows above keyword
 

Brian,

Need to test row number by adding another if ....

Sorry I didn't catch that...

If rng1.row 1 then
Range(Rows(1), Rows(rng1.Row - 1)).Delete
End If


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
If rng1.row 1 then
Range(Rows(1), Rows(rng1.Row - 1)).Delete
End If
Else
MsgBox "Not found"
End If

--
sb
"Brian" wrote in message
...
Steve,

do you know if there is a way to modify this so that if my
keyword happens to be in cell A1 the macro will not error
out?



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




.