shortening code

[carlleese24]

hi

this is the code I have

Sub Test3()

        

    Dim i As Long
    Dim colL As Range
    Dim x As Long
    Dim n As Long
    Dim colN As Range
    Dim ColQ As Range
    Dim colT As Range
    Dim colW As Range
    Dim colZ As Range
    Dim colAC As Range
    Dim colAK As Range
    Dim colAL As Range
    Dim colAM As Range
    Dim colAN As Range
    Dim colAO As Range
    Dim colAP As Range
    Dim colAQ As Range
    Dim colAR As Range
    Dim colAW As Range
    Dim colAX As Range
    Dim colAY As Range
    Dim colAZ As Range
    Dim colBA As Range
    Dim colBB As Range
    Dim colBC As Range
    Dim colBD As Range
    Dim colBJ As Range
    Dim colBK As Range
    Dim colBL As Range
    Dim colBM As Range
    Dim colBN As Range
    Dim colBO As Range
    Dim colBP As Range
    



















For i = 2 To Sheets("sheet2").Range("iu65536").End(xlUp).Row




cellval = Sheets("sheet2").Range("iv" & i).Value
cellval2 = Sheets("sheet2").Range("iu" & i).Value


'colL'
Set Rng = Sheets("sheet2").Range("l" & cellval & ":l" & cellval2)
'colN'
Set Rng1 = Sheets("sheet2").Range("n" & cellval & ":n" & cellval2)
'ColQ'
Set Rng2 = Sheets("sheet2").Range("q" & cellval & ":q" & cellval2)
'ColT'
Set Rng3 = Sheets("sheet2").Range("T" & cellval & ":T" & cellval2)
'ColW'
Set Rng4 = Sheets("sheet2").Range("W" & cellval & ":W" & cellval2)
'ColZ'
Set Rng5 = Sheets("sheet2").Range("Z" & cellval & ":Z" & cellval2)
'ColAC'
Set Rng6 = Sheets("sheet2").Range("AC" & cellval & ":AC" & cellval2)
'ColAK'
Set Rng7 = Sheets("sheet2").Range("AK" & cellval & ":AK" & cellval2)
'ColAL'
Set Rng8 = Sheets("sheet2").Range("AL" & cellval & ":AL" & cellval2)
'ColAM'
Set Rng9 = Sheets("sheet2").Range("AM" & cellval & ":AM" & cellval2)
'ColAN'
Set Rng10 = Sheets("sheet2").Range("AN" & cellval & ":AN" & cellval2)
'ColAO'
Set Rng11 = Sheets("sheet2").Range("AO" & cellval & ":AO" & cellval2)
'ColAP'
Set Rng12 = Sheets("sheet2").Range("AP" & cellval & ":AP" & cellval2)
'ColAQ'
Set Rng13 = Sheets("sheet2").Range("AQ" & cellval & ":AQ" & cellval2)
'ColAR'
Set Rng14 = Sheets("sheet2").Range("AR" & cellval & ":AR" & cellval2)
'ColAW'
Set Rng15 = Sheets("sheet2").Range("AW" & cellval & ":AW" & cellval2)
'ColAX'
Set Rng16 = Sheets("sheet2").Range("AX" & cellval & ":AX" & cellval2)
'ColAY'
Set Rng17 = Sheets("sheet2").Range("AY" & cellval & ":AY" & cellval2)
'CoAZ'
Set Rng18 = Sheets("sheet2").Range("AZ" & cellval & ":AZ" & cellval2)
'ColBA'
Set Rng19 = Sheets("sheet2").Range("BA" & cellval & ":BA" & cellval2)
'ColBB'
Set Rng20 = Sheets("sheet2").Range("BB" & cellval & ":BB" & cellval2)
'ColBC'
Set Rng21 = Sheets("sheet2").Range("BC" & cellval & ":BC" & cellval2)
'ColBD'
Set Rng22 = Sheets("sheet2").Range("BD" & cellval & ":BD" & cellval2)
'ColBJ'
Set Rng23 = Sheets("sheet2").Range("BJ" & cellval & ":BJ" & cellval2)
'ColBK'
Set Rng24 = Sheets("sheet2").Range("BK" & cellval & ":BK" & cellval2)
'ColBL'
Set Rng25 = Sheets("sheet2").Range("BL" & cellval & ":BL" & cellval2)
'ColBM'
Set Rng26 = Sheets("sheet2").Range("BM" & cellval & ":BM" & cellval2)
'ColBN'
Set Rng27 = Sheets("sheet2").Range("BN" & cellval & ":BN" & cellval2)
'ColBO'
Set Rng28 = Sheets("sheet2").Range("BO" & cellval & ":BO" & cellval2)
'ColBP'
Set Rng29 = Sheets("sheet2").Range("BP" & cellval & ":BP" & cellval2)
 
 
 
For Each colBP In Rng29

        x = Application.Rank(colBP.Value, Rng29)
        n = Application.Count(colBP.Value, Rng29)


        colBP.Offset(, 1) = x

Next colBP
 
 
 
 
For Each colBO In Rng28

        x = Application.Rank(colBO.Value, Rng28)
        n = Application.Count(colBO.Value, Rng28)


        colBO.Offset(, 1) = x

Next colBO
 
For Each colBN In Rng27

        x = Application.Rank(colBN.Value, Rng27)
        n = Application.Count(colBN.Value, Rng27)


        colBN.Offset(, 1) = x

Next colBN
For Each colBM In Rng26

        x = Application.Rank(colBM.Value, Rng26)
        n = Application.Count(colBM.Value, Rng26)


        colBM.Offset(, 1) = x

Next colBM
For Each colBL In Rng25

        x = Application.Rank(colBL.Value, Rng25)
        n = Application.Count(colBL.Value, Rng25)


        colBL.Offset(, 1) = x

Next colBL
For Each colBK In Rng24

        x = Application.Rank(colBK.Value, Rng24)
        n = Application.Count(colBK.Value, Rng24)


        colBK.Offset(, 1) = x

Next colBK
For Each colBJ In Rng23

        x = Application.Rank(colBJ.Value, Rng23)
        n = Application.Count(colBJ.Value, Rng23)


        colBJ.Offset(, 1) = x

Next colBJ
For Each colBD In Rng22

        x = Application.Rank(colBD.Value, Rng22)
        n = Application.Count(colBD.Value, Rng22)


        colBD.Offset(, 1) = x

Next colBD
For Each colBC In Rng21

        x = Application.Rank(colBC.Value, Rng21)
        n = Application.Count(colBC.Value, Rng21)


        colBC.Offset(, 1) = x

Next colBC
For Each colBB In Rng20

        x = Application.Rank(colBB.Value, Rng20)
        n = Application.Count(colBB.Value, Rng20)


        colBB.Offset(, 1) = x

Next colBB
For Each colBA In Rng19

        x = Application.Rank(colBA.Value, Rng19)
        n = Application.Count(colBA.Value, Rng19)


        colBA.Offset(, 1) = x

Next colBA
For Each CoAZ In Rng18

        x = Application.Rank(CoAZ.Value, Rng18)
        n = Application.Count(CoAZ.Value, Rng18)


        CoAZ.Offset(, 1) = x

Next CoAZ
For Each colAY In Rng17

        x = Application.Rank(colAY.Value, Rng17)
        n = Application.Count(colAY.Value, Rng17)


        colAY.Offset(, 1) = x

Next colAY
For Each colAX In Rng16

        x = Application.Rank(colAX.Value, Rng16)
        n = Application.Count(colAX.Value, Rng16)


        colAX.Offset(, 1) = x

Next colAX
For Each colAW In Rng15

        x = Application.Rank(colAW.Value, Rng15)
        n = Application.Count(colAW.Value, Rng15)


        colAW.Offset(, 1) = x

Next colAW
For Each colAR In Rng14

        x = Application.Rank(colAR.Value, Rng14)
        n = Application.Count(colAR.Value, Rng14)


        colAR.Offset(, 1) = x

Next colAR
For Each colAQ In Rng13

        x = Application.Rank(colAQ.Value, Rng13)
        n = Application.Count(colAQ.Value, Rng13)


        colAQ.Offset(, 1) = x

Next colAQ
For Each colAP In Rng12

        x = Application.Rank(colAP.Value, Rng12)
        n = Application.Count(colAP.Value, Rng12)


        colAP.Offset(, 1) = x

Next colAP
For Each colAO In Rng11

        x = Application.Rank(colAO.Value, Rng11)
        n = Application.Count(colAO.Value, Rng11)


        colAO.Offset(, 1) = x

Next colAO
For Each colAN In Rng10

        x = Application.Rank(colAN.Value, Rng10)
        n = Application.Count(colAN.Value, Rng10)


        colAN.Offset(, 1) = x

Next colAN
For Each colAM In Rng9

        x = Application.Rank(colAM.Value, Rng9)
        n = Application.Count(colAM.Value, Rng9)


        colAM.Offset(, 1) = x

Next colAM
For Each colAL In Rng8

        x = Application.Rank(colAL.Value, Rng8)
        n = Application.Count(colAL.Value, Rng8)


        colAL.Offset(, 1) = x

Next colAL
For Each colAK In Rng7

        x = Application.Rank(colAK.Value, Rng7)
        n = Application.Count(colAK.Value, Rng7)


        colAK.Offset(, 1) = x

Next colAK
For Each colAC In Rng6

        x = Application.Rank(colAC.Value, Rng6)
        n = Application.Count(colAC.Value, Rng6)


        colAC.Offset(, 1) = x

Next colAC

For Each colZ In Rng5

        x = Application.Rank(colZ.Value, Rng5)
        n = Application.Count(colZ.Value, Rng5)


        colZ.Offset(, 1) = x

Next colZ

For Each colW In Rng4

        x = Application.Rank(colW.Value, Rng4)
        n = Application.Count(colW.Value, Rng4)


        colW.Offset(, 1) = x

Next colW
 
 

For Each colT In Rng3

        x = Application.Rank(colT.Value, Rng3)
        n = Application.Count(colT.Value, Rng3)


        colT.Offset(, 1) = x

Next colT






For Each ColQ In Rng2

        x = Application.Rank(ColQ.Value, Rng2)
        n = Application.Count(ColQ.Value, Rng2)


        ColQ.Offset(, 1) = x

Next ColQ


For Each colN In Rng1

        x = Application.Rank(colN.Value, Rng1)
        n = Application.Count(colN.Value, Rng1)


        colN.Offset(, 1) = x

Next colN





For Each colL In Rng

        x = Application.Rank(colL.Value, Rng)
        n = Application.Count(colL.Value, Rng)


        colL.Offset(, 1) = x
Next colL





Next i


End Sub

it works perfectly but can anyone tell me how I can shorten it please


from Carl

 

[cherria]

You'd be better of telling us what you want the macro to do and then letting somebody have a stab at writing it from scratch, rather than expecting us to wade through your existing macro, figure out what it does and shorten it.

 

[jindon]

Hi

hope this works

Sub test()
Dim i As Long, ii As Integer, rngArray() As Range
Dim x, n, cellval, cellval2, r As Range
Dim colArray
colArray = Array("L", "N", "Q", "T", "W", "Z", "AC", "AK", "AL", "AM", "AN", _
"AO", "AP", "AQ", "AR", "AW", "AX", "AY", "AZ", "BA", "BB", "BC", "BD", "BJ", _
"BK", "BL", "BM", "BN", "BO", "BP")
ReDim Preserve colArray(1 To UBound(colArray) + 1)
With Sheets("sheet2")
    For i = 2 To .Range("iu65536").End(xlUp).Row
        cellval = Sheets("sheet2").Range("iv" & i).Value
        cellval2 = Sheets("sheet2").Range("iu" & i).Value
        ReDim rngArray(1 To 30)
        For ii = LBound(colArray) To UBound(colArray)
            Set rngArray(ii) = .Range(.Cells(cellval, colArray(ii)), _
                .Cells(cellval2, colArray(ii)))
        Next
        For ii = UBound(rngArray) To LBound(rngArray) Step -1
            For Each r In rngArray(ii)
                x = Application.Rank(r.Value, rngArray(ii))
                n = Application.Count(r.Value, rngArray(ii))
                r.Offset(, 1) = x
            Next
        Next
    Next
   Erase rngArray
End With
End Sub

 

[carlleese24]

hi

thank you very much Jindon the code that you did works perfectly.

the code ranks data in 30 colums and gets its references from IU and the IV columns

from carl

 

[carlleese24]

hi

thank you very much Jindon the code that you did works perfectly.

the code ranks data in 30 colums and gets its references from IU and the IV columns

from carl