let say i have formula as below
A1 - 5 (=b1*c1)
A2 - 5 (=b2*c2)
A3 - 5 (=b3*b3)
A4 - 0 (=b4*b4) (can be zero or blank)

i wanted my mcro to copy from A1-A4 but only paste A1-A3, b'coz A4 is not
value to calculate.

thanks in advance

Try

Sub stantial()
Dim myrange As Range, NonZero As Range
Set myrange = Range("a1", Cells(Rows.Count, "a").End(xlUp))
Set NonZero = myrange(myrange.Rows.Count, myrange.Columns.Count)

Do Until NonZero 0
Set NonZero = myrange.Find(What:="*", After:=NonZero,
LookIn:=xlFormulas, LookAt _
:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlPrevious)
Loop
Range("A1:" & NonZero.Address).Copy
End Sub

Mike

"ramzi" wrote:

let say i have formula as below
A1 - 5 (=b1*c1)
A2 - 5 (=b2*c2)
A3 - 5 (=b3*b3)
A4 - 0 (=b4*b4) (can be zero or blank)

i wanted my mcro to copy from A1-A4 but only paste A1-A3, b'coz A4 is not
value to calculate.

thanks in advance

correction

Set NonZero = myrange(myrange.Rows.Count)


Mike

"Mike H" wrote:

Try

Sub stantial()
Dim myrange As Range, NonZero As Range
Set myrange = Range("a1", Cells(Rows.Count, "a").End(xlUp))
Set NonZero = myrange(myrange.Rows.Count, myrange.Columns.Count)

Do Until NonZero 0
Set NonZero = myrange.Find(What:="*", After:=NonZero,
LookIn:=xlFormulas, LookAt _
:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlPrevious)
Loop
Range("A1:" & NonZero.Address).Copy
End Sub

Mike

"ramzi" wrote:

let say i have formula as below
A1 - 5 (=b1*c1)
A2 - 5 (=b2*c2)
A3 - 5 (=b3*b3)
A4 - 0 (=b4*b4) (can be zero or blank)

i wanted my mcro to copy from A1-A4 but only paste A1-A3, b'coz A4 is not
value to calculate.

thanks in advance

hi Mike,
Thanks a lot, it works with one coloum. and my data actually in 7coloum and
different range of location as below (the example given earlier is A1:A4)

D8:J14 and O8:O14.

If my data is 0 at row 13 and 14 the macro will copy only D8:D12 and O8:O12.
I try to modify ur macro ealier but fails
I need your help again on this...:)
thanks

..

"Mike H" wrote:

correction

Set NonZero = myrange(myrange.Rows.Count)


Mike

"Mike H" wrote:

Try

Sub stantial()
Dim myrange As Range, NonZero As Range
Set myrange = Range("a1", Cells(Rows.Count, "a").End(xlUp))
Set NonZero = myrange(myrange.Rows.Count, myrange.Columns.Count)

Do Until NonZero 0
Set NonZero = myrange.Find(What:="*", After:=NonZero,
LookIn:=xlFormulas, LookAt _
:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlPrevious)
Loop
Range("A1:" & NonZero.Address).Copy
End Sub

Mike

"ramzi" wrote:

let say i have formula as below
A1 - 5 (=b1*c1)
A2 - 5 (=b2*c2)
A3 - 5 (=b3*b3)
A4 - 0 (=b4*b4) (can be zero or blank)

i wanted my mcro to copy from A1-A4 but only paste A1-A3, b'coz A4 is not
value to calculate.

thanks in advance