I did whatt you asked for. Not sure is it really makes sense. You should be
able to make some changes on your own to get it right.



Sub newsplit()
Dim myRange As Range
Dim r As Range
Dim d As Variant


LastCol = Cells(1, Columns.Count).End(xlToLeft).Column
LastRow = Cells(Rows.Count, "A").End(xlUp).Row

LastEmptyRow = 2
For RowCount = 2 To (LastRow - 1) Step 2

If IsEmpty(Cells(RowCount, "A")) Then
Set EmptyRange = Range(Cells(LastEmptyRow, "A"), _
Cells(RowCount - 1, "B"))
Lastrow1 = Cells(Rows.Count, "C").End(xlUp).Row
EmptyRange.Copy Destination:=Range("C" & (Lastrow1 + 1))

Else
d = Sqr((Cells(RowCount + 1, "A") - Cells(RowCount, "A")) ^ 2 + _
(Cells(RowCount + 1, "B") - Cells(RowCount, "B")) ^ 2)

For ColumnCount = 3 To LastCol Step 2

If ColumnCount = LastCol Then

Lastrow1 = Cells(Rows.Count, ColumnCount).End(xlUp).Row
Cells(Lastrow1 + 1, ColumnCount) = Cells(RowCount, "A")
Cells(Lastrow1 + 1, ColumnCount + 1) = Cells(RowCount, "B")
Cells(Lastrow1 + 2, ColumnCount) = Cells(RowCount + 1, "A")
Cells(Lastrow1 + 2, ColumnCount + 1) = Cells(RowCount + 1, "B")

Else
If Abs(d) = Cells(1, ColumnCount) And _
Abs(d) < Cells(1, ColumnCount + 2) Then

Lastrow1 = Cells(Rows.Count, ColumnCount).End(xlUp).Row
Cells(Lastrow1 + 1, ColumnCount) = Cells(RowCount, "A")
Cells(Lastrow1 + 1, ColumnCount + 1) = Cells(RowCount, "B")
Cells(Lastrow1 + 2, ColumnCount) = Cells(RowCount + 1, "A")
Cells(Lastrow1 + 2, ColumnCount + 1) = Cells(RowCount + 1, "B")
Exit For
End If
End If
Next ColumnCount
End If
Next RowCount

End Sub


" wrote:

hi joel once again thanx for ur code

i tried it but its not actually wat i want to do with data ...2day is
4th day i m spending on this problem but want to solve it anyhow i m
putting my querry here aonce again

i hav divided it into two parts
plz if u progrmme these two conditions i wil b really thankful 2 u

One idea/logic to approach this problem but unable to programme it as
i am a novice

1] why dont we insert an empty cell above the value which gives
whenevr "D" 10

"D" = Sqr((r.Value - r.Offset(-1, 0).Value) ^ 2 + (r.Offset(0,
1).Value - r.Offset(-1, 1).Value) ^ 2)

say if "D" = 11 for cell a90 , b90 & a 91 , b91 value......then insert
cell above a91 and b 91

similarly wherevr we get "d" 10

2} Now the second part, copy all the value before first empty cel
occurs say in row a91 empty cell ocurs so code shud copy all values
above empty cell n paste the a1:b91 values in col.c n col.d [c1:d91]

like wise if again empty cell occur at a140 then copy values from
a92:b139 n paste them to col .e & col.f [E1:F47]