use macro to copy formula with value and not formula without value
 

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

use macro to copy formula with value and not formula without value
 

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

use macro to copy formula with value and not formula without v
 

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

use macro to copy formula with value and not formula without v
 

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