Macro Question

  • Thread starter Thread starter Dave Coz
  • Start date Start date
D

Dave Coz

Hello,
I have a macro that manipulates a text file and converts into excel. There
is a header record and trailer record that I use the "find" function to find
and delete. I then select the first row of data and use the 'xldown'
selection method to get the rest of the data. Problem is that every so often
there is either no data, or only one row. In these cases excel tries to
select the entire worksheet. Does anyone have a solution that would allow
the macro to detect that there is only one row of data? Or if there are no
rows of data?
Here is a portion of the code:

Range("a1").Select
Cells.Find(What:="FHDR", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False _
, SearchFormat:=False).Activate
Selection.EntireRow.Delete
Range(Selection, Selection.End(xlDown)).Select

Any help you can offer is greatly appreciated.

Dave
 
Replace

Range(Selection, Selection.End(xlDown)).Select

with

If IsEmpty(ActiveCell.Offset(1)) Then
MsgBox "There is NO data in the next row"
Else
MsgBox ActiveCell.Offset(1).Value
Range(Selection, Selection.End(xlDown)).Select
End If

Hope this helps. Good Luck.
 
ND Pard,
Thanks so much.

Dave

ND Pard said:
Replace

Range(Selection, Selection.End(xlDown)).Select

with

If IsEmpty(ActiveCell.Offset(1)) Then
MsgBox "There is NO data in the next row"
Else
MsgBox ActiveCell.Offset(1).Value
Range(Selection, Selection.End(xlDown)).Select
End If

Hope this helps. Good Luck.
Click to expand...
 
Back
Top