Test for blank lines
 

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

Test for blank lines
 

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

Test for blank lines
 

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