optimize VBA CODE help pls

[luvbite38]

Hi Can someone kindly help me optimize this code pls.....is there a way to run this code in the fastest possible way???

Tonnes of thanks in advance

Range("lj51:lj887") = "=IF(RC[-164]="""","""",RC[-164])"
Range("lj51:lj887").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Selection.TextToColumns Destination:=Range("LJ51"), DataType:=xlFixedWidth _
, FieldInfo:=Array(0, 1), TrailingMinusNumbers:=True


Range("LJ89:LJ113").Cut Destination:=Range("LK51:LK75")
Range("LJ114:LJ138").Cut Destination:=Range("LL51:LL75")
Range("LJ139:LJ163").Cut Destination:=Range("LM51:LM75")
Range("LJ164:LJ187").Cut Destination:=Range("LN51:LN74")
Range("LJ188:LJ211").Cut Destination:=Range("LO51:LO74")
Range("LJ863:LJ888").Cut Destination:=Range("LP51:LP76")
Range("LJ51:LP88").SpecialCells(xlCellTypeBlanks).Select
Selection.Delete Shift:=xlUp


Range("KY915:LH935").Copy
Range("LJ915").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False



Range("LK915:LK935").Cut Destination:=Range("LJ936:LJ956")
Range("LL915:LL935").Cut Destination:=Range("LJ957:LJ977")
Range("LM915:LM935").Cut Destination:=Range("LJ977:LJ997")
Range("LN915:LN935").Cut Destination:=Range("LJ998:LJ1018")
Range("LO915:LO935").Cut Destination:=Range("LJ1019:LJ1039")
Range("LP915:LP935").Cut Destination:=Range("LJ1040:LJ1060")
Range("LQ915:LQ935").Cut Destination:=Range("LJ1061:LJ1081")
Range("LR915:LR935").Cut Destination:=Range("LJ1082:LJ1102")
Range("LS915:LS935").Cut Destination:=Range("LJ1103:LJ1123")

Range("LJ915:LJ1123").TextToColumns Destination:=Range("LJ915"), DataType:=xlFixedWidth _
, FieldInfo:=Array(0, 1), TrailingMinusNumbers:=True

Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.Delete Shift:=xlUp
Range("LJ915:LJ924").Select
Selection.Copy
Range("LQ51").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False



Range("LJ51:LJ63").Copy
Range("LJ2").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Range("LK51:LK60").Copy
Range("LW2").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Range("LL51:LL60").Copy
Range("MG2").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Range("LM51:LM60").Copy

Range("MQ2").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True

Range("LN51:LN60").Copy

Range("NA2").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True

Range("LO51:LO60").Copy

Range("NK2").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True

Range("LP51:LP60").Copy

Range("NU2").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True

Range("LQ51:LQ60").Copy

Range("OE2").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Application.CutCopyMode = False

 

[wigi]

Start by taking out the Select's. E.g.

Range("LJ51:LP88").SpecialCells(xlCellTypeBlanks).Select
Selection.Delete Shift:=xlUp

becomes

Range("LJ51:LP88").SpecialCells(xlCellTypeBlanks).Delete xlUp

 

[luvbite38]

thanks Wigi,
I'll give it a go....I am very new to Macros... is there any other suggestions to optimise the formula???

Many Thanks

 

[wigi]

Sure. Use (in pseudo-code):

Range("A").Value = Range("B").Value

to copy from B to A. Make sure formatting is okay, so that copying values is only needed (in fact, transferring values).

Also, turn off screenupdating and set calculations to manual. Although there are other ways to prevent screen flickering.

Wigi