efficient code

  • Hi,, is there a more efficient way to write this/??? Just curious.. If i increase the range excel crashes.. so i had to do a copy/paste about 5-6 times, but it's obviously not efficient. wondering a better way to go about getting the same thing done? Thanks!


  • Quote


    Range("jw10").Select


    ?


    Range JW10 is impossible in all my versions of Excel.... my columns stop at IV - umm have I messed something really obvious ?


    Wasn't quite sure whether that was a contigous range or not row 10 to 9948 but here's some code for column IV that would work quicker... maybe you will get the idea ?


    Sub jsttFormula()

    Dim llastRw As Long


    llastRw = 9948
    With Worksheets("Sheet4").Range("IV10")
    .Formula = "=VLOOKUP(RC14,'[bs.xls]BS'!R2C2:R2000C5,4,FALSE)"
    .AutoFill Destination:=Worksheets("Sheet4") _
    .Range("IV10:IV" & llastRw&)


    End With
    End Sub


    Hope this helps

  • sorry will, they are named ranges w/in the sheet. Every once in a while I have to add new rows in sorta random places.. so i was sick of changing the cell references.. So i gave them names. this way if i add something i can just add jw9950 etc... anyways.. i assume this changes ur code since they are not all in a row that i need the code in, they skip around... thanks!

  • You can assign the formula directly to all of your ranges.


    You may also want to see if putting the calculations in manual mode first helps the speed any.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!