Hello,

I have a macro that reformats a raw data dump for a third party application.
The user creating the data dump chooses what information to include in the
dump and this is causing me some problems.

I need some code that will look for the column header called "Claim No" ,
and then delete any rows above the row containing the "Claim No" label.

Any help will be gratefully appreciated.
--
Thanks,
MarkN

Most of my column headers are in row 1.

but maybe...

Option Explicit
Sub testme01()

Dim RngToSearch As Range
Dim LookForWhat As String
Dim FoundCell As Range

LookForWhat = "Claim No"

With Worksheets("sheet1")
Set RngToSearch = .Range("a:a") 'I used column A.
With RngToSearch
Set FoundCell = .Cells.Find(what:=LookForWhat, _
after:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
lookat:=xlWhole, _
searchorder:=xlByRows, _
searchdirection:=xlNext, _
MatchCase:=False)
End With

If FoundCell Is Nothing Then
MsgBox "Not Found"
Else
If FoundCell.Row = 1 Then
MsgBox "Found in row 1--not deleted!"
Else
.Range("A1:A" & FoundCell.Row - 1).EntireRow.Delete
End If
End If
End With

End Sub

MarkN wrote:

Hello,

I have a macro that reformats a raw data dump for a third party application.
The user creating the data dump chooses what information to include in the
dump and this is causing me some problems.

I need some code that will look for the column header called "Claim No" ,
and then delete any rows above the row containing the "Claim No" label.

Any help will be gratefully appreciated.
--
Thanks,
MarkN

--

Dave Peterson

Hello Dave,

You're a genius, thanks very much.
--
Thanks,
MarkN


"Dave Peterson" wrote:

Most of my column headers are in row 1.

but maybe...

Option Explicit
Sub testme01()

Dim RngToSearch As Range
Dim LookForWhat As String
Dim FoundCell As Range

LookForWhat = "Claim No"

With Worksheets("sheet1")
Set RngToSearch = .Range("a:a") 'I used column A.
With RngToSearch
Set FoundCell = .Cells.Find(what:=LookForWhat, _
after:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
lookat:=xlWhole, _
searchorder:=xlByRows, _
searchdirection:=xlNext, _
MatchCase:=False)
End With

If FoundCell Is Nothing Then
MsgBox "Not Found"
Else
If FoundCell.Row = 1 Then
MsgBox "Found in row 1--not deleted!"
Else
.Range("A1:A" & FoundCell.Row - 1).EntireRow.Delete
End If
End If
End With

End Sub

MarkN wrote:

Hello,

I have a macro that reformats a raw data dump for a third party application.
The user creating the data dump chooses what information to include in the
dump and this is causing me some problems.

I need some code that will look for the column header called "Claim No" ,
and then delete any rows above the row containing the "Claim No" label.

Any help will be gratefully appreciated.
--
Thanks,
MarkN

--

Dave Peterson