Another possibility:

Option Explicit

Sub CopyMeaningfulDAta()
Dim SrcSht As Worksheet, DEstSht As Worksheet
Dim rng as Range
With ThisWorkbook
Set SrcSht = .Sheets("Sheet1")
Set DEstSht = .Sheets("Sheet2")
End With
set rng = srcsht.columns(2).SpecialCells(xlFormulas,xltextva lues)
if Not rng is nothing then
rng.Entirerow.copy Destination:=DestSht.Range("A1")
End if
End Sub

--
Regards,
Tom Ogilvy



"Hayes0" wrote in message
...
Option Explicit


Dim SrcSht As Worksheet, DEstSht As Worksheet
Dim SrcRange As Range, SrcRow As Range, SrcCell As Range
Dim DestRow As Single, ValidRow As Boolean


Sub CopyMeaningfulDAta()
With ThisWorkbook
Set SrcSht = .Sheets("Sheet1")
Set DEstSht = .Sheets("Sheet2")
End With
Set SrcRange = SrcSht.Cells(1, 2).CurrentRegion
If SrcRange.Rows.Count 1 Then
DestRow = 0
For Each SrcRow In SrcRange.Rows
ValidRow = True
For Each SrcCell In SrcRow.Cells
If IsError(SrcCell) Then
ValidRow = False
Else
Select Case IsNumeric(SrcCell)
Case True
If SrcCell = 0 Then ValidRow =
False
Case False
If SrcCell = "" Or IsError
(SrcCell) Then
ValidRow = False
End If
End Select
End If
Next
If ValidRow Then
DestRow = DestRow + 1
For Each SrcCell In SrcRow.Cells
DEstSht.Cells(DestRow, SrcCell.Column)
= SrcCell
Next
End If
Next
End If

End Sub
-----Original Message-----
Hi,

I am trying to copy rows that only have meaningful data
in them. I
want to do this in VBA.

For example I have the following data in Sheet2 (data
derived from
vlookups and = formulas):

Emp No Name DOB Grade
12345 Rachel 28/02/78 A
45678 Debbie 15/09/82 A
54872 David 11/05/69 B
#N/A #N/A 0 0
#N/A #N/A 0 0

I only want to copy and paste the rows that have
meaningful data - in
the above example this would be rows 1, 2 and 3. The
amount of rows
will change each time the vba code is ran. So there could
be more or
less rows each time.

How do I write in VBA a formula to say if cells have #N/A
or 0 in them
do not copy these rows into a new worksheet, only copy
meaningful
data.

Any help would be appreciated

Kind Regards

R Curran
.