shortening code

C

carlleese24

Board Regular
Joined
Mar 15, 2005
Messages
108
hi

this is the code I have

Code: 
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
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
Sort by date Sort by votes
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.
 
Upvote 0
Hi

hope this works
Code: 
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Solved
Find column and delete rows below....
2
Replies
11
Views
594
Alex Blakenburg
A
G
  • Question
VBA help - copy cells from specific table to specific table on another sheet.
Replies
0
Views
327
GardianAngelUK
G
rjplante
  • Solved
sort numbers with letters
2 3
Replies
24
Views
421
Akuini
Akuini
H
  • Solved
Amend the VBA code to Filter Columns Instead of Rows
Replies
9
Views
195
Akuini
Akuini
C
  • Question
A Bookmark to return to the same row when exiting and opening the application
Replies
8
Views
186
Edgar_
Edgar_

Forum statistics

Threads
1,215,503
Messages
6,125,179
Members
449,212
Latest member
kenmaldonado

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top