Test for blank lines

[MarkN]

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]

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

[MarkN]

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
>