how to optimize this?

Posted by zac on April 27, 2001 7:50 AM

i have columns say I, J, K and i want to shift data one column to the right like this: data in column K disappears, data from column J moves to column K, data from column I moves to column J, and column I is filled with zero's. i'm doing it this way:

Application.ScreenUpdating = False

Range("J31:J32").Copy
Range("K31").PasteSpecial Paste:=xlValues
Range("I31:I32").Copy
Range("J31").PasteSpecial Paste:=xlValues
Range("I31:I32").Value = 0

Range("J34:J37").Copy
Range("K34").PasteSpecial Paste:=xlValues
Range("I34:I37").Copy
Range("J34").PasteSpecial Paste:=xlValues
Range("I34:I37").Value = 0

and so on...

i can't move it all in one chunk because some rows have array formulas and can't be moved. is there a better (faster) way to do this? thanks!

Posted by Dave Hawley on April 28, 2001 3:23 AM

Hi Zac

Sub TryThis()
Range("K31:K32") = Range("J31:J32").Value
Range("J31:J32") = Range("I31:I32").Value
Range("I31:I32").Value = 0

Range("K34:K37") = Range("J34:J37").Value
Range("I34:I37").Value = 0

End Sub

Dave
OzGrid Business Applications