Shorten code...possible?

[bemcbride]

So i've gotten my code to work and it works great BUT it's going to be VERY long.

At the end I'll have like 80+ Case indexs (I have to copy the code about 80 times and change the range.) Is there a shorter way to do this? I can change the data on the sheet into tables or something and name them a number and have it loop to the next table on the indicated sheet?

I'm just trying to avoid copying this 80 plus times and changing the range.


Private Sub ComboBox2_Change()
Dim index As Integer
index = ComboBox2.ListIndex


ComboBox3.Clear


Select Case index
Case Is = 0
With ComboBox3
Me.ComboBox3.List = Worksheets("SicRevised").Range("G4:G9").Value
End With

Case Is = 1
With ComboBox3
Me.ComboBox3.List = Worksheets("SicRevised").Range("G10:G14").Value
End With

Case Is = 2
With ComboBox3
Me.ComboBox3.List = Worksheets("SicRevised").Range("G15:G20").Value
End With

Case Is = 3
With ComboBox3
Me.ComboBox3.List = Worksheets("SicRevised").Range("G21:G23").Value
End With

Case Is = 4
With ComboBox3
Me.ComboBox3.List = Worksheets("SicRevised").Range("G24:G26").Value
End With

Case Is = 5
With ComboBox3
Me.ComboBox3.List = Worksheets("SicRevised").Range("G27:G33").Value
End With

Case Is = 6
With ComboBox3
Me.ComboBox3.List = Worksheets("SicRevised").Range("G34:G6").Value
End With

Case Is = 7
With ComboBox3
Me.ComboBox3.List = Worksheets("SicRevised").Range("G37:G39").Value
End With

Case Is = 8
With ComboBox3
Me.ComboBox3.List = Worksheets("SicRevised").Range("G40:G46").Value
End With

Case Is = 9
With ComboBox3
Me.ComboBox3.List = Worksheets("SicRevised").Range("G47:G49").Value
End With

Case Is = 10
With ComboBox3
Me.ComboBox3.List = Worksheets("SicRevised").Range("G50:G51").Value
End With

Case Is = 11
With ComboBox3
Me.ComboBox3.List = Worksheets("SicRevised").Range("G52:G60").Value
End With

Case Is = 12
With ComboBox3
Me.ComboBox3.List = Worksheets("SicRevised").Range("G61:G69").Value
End With

Case Is = 13
With ComboBox3
Me.ComboBox3.List = Worksheets("SicRevised").Range("G70:G73").Value
End With

Case Is = 14
With ComboBox3
Me.ComboBox3.List = Worksheets("SicRevised").Range("G74:G82").Value
End With

Case Is = 15
With ComboBox3
Me.ComboBox3.List = Worksheets("SicRevised").Range("G83:G91").Value
End With

Case Is = 17
With ComboBox3
Me.ComboBox3.List = Worksheets("SicRevised").Range("G92:G97").Value
End With

Case Is = 18
With ComboBox3
Me.ComboBox3.List = Worksheets("SicRevised").Range("G98:G102").Value
End With

Case Is = 19
With ComboBox3
Me.ComboBox3.List = Worksheets("SicRevised").Range("G103:G107").Value
End With

Case Is = 20
With ComboBox3
Me.ComboBox3.List = Worksheets("SicRevised").Range("G108:G116").Value
End With

Case Is = 21
With ComboBox3
Me.ComboBox3.List = Worksheets("SicRevised").Range("G117:G124").Value
End With

Case Is = 22
With ComboBox3
Me.ComboBox3.List = Worksheets("SicRevised").Range("G125:G127").Value
End With


Case Is = 23
With ComboBox3
Me.ComboBox3.List = Worksheets("SicRevised").Range("G128:G132").Value
End With

Case Is = 24
With ComboBox3
Me.ComboBox3.List = Worksheets("SicRevised").Range("G133:G138").Value
End With

Case Is = 25
With ComboBox3
Me.ComboBox3.List = Worksheets("SicRevised").Range("G139:G148").Value
End With

Case Is = 26
With ComboBox3
Me.ComboBox3.List = Worksheets("SicRevised").Range("G149:G155").Value
End With

Case Is = 27
With ComboBox3
Me.ComboBox3.List = Worksheets("SicRevised").Range("G156:G164").Value
End With

Case Is = 28
With ComboBox3
Me.ComboBox3.List = Worksheets("SicRevised").Range("G165:G173").Value
End With

Case Is = 29
With ComboBox3
Me.ComboBox3.List = Worksheets("SicRevised").Range("G174:G181").Value
End With
End Select
End Sub

 

[Puertorekinsam]

Couple of things you can do somthing like this:

With ComboBox3
Me.ComboBox3.List = Worksheets("SicRevised").Range("G174:G181").Value
End With

should be:

With ComboBox3
.List = Worksheets("SicRevised").Range("G174:G181").Value
End With

otherwise, you are actually not using the with statement. Likewise you should move the with statement to outside the entire case block since they all work with combobox3.

Unfortunately, since there doesn't seem to be much rhyme or reason to your ranges... some are 6 cells, some are 5 cells, etc... I'm not sure of how to shorten this code more than that.

 

[bemcbride]

What if I changed the way the range is? All the values are in column G What if i separated the groups by putting group A in to column A group b into column b etc?

 

[timfritsch]

If you put your lists in separate columns, you could refer to that column rather than a group of rows in a single column.

Could work something like (code not tested):

dim lastRow as long

index = ComboBox2.ListIndex
lastrow = Worksheets("SicRevised").Range(rows.count,"A").offset(,index).end(xlup).row

ComboBox3.Clear

Me.ComboBox3.List = Worksheets("SicRevised").Range("A1").offset(0,index).resize(lastrow,1)

Tim

 

[Rick Rothstein]

If I am not mistaken, the following should be able to replace the ComboBox2 Change event code you posted originally...

[SIZE=1]Private Sub ComboBox2_Change()
  Dim Addr As String
  Addr = "G4:G9 G10:G14 G15:G20 G21:G23 G24:G26 G27:G33 G34:G6 G37:G39 " & _
         "G40:G46 G47:G49 G50:G51 G52:G60 G61:G69 G70:G73 G74:G82 G83:G91 " & _
         "G92:G97 G98:G102 G103:G107 G108:G116 G117:G124 G125:G127 G128:G132 " & _
         "G133:G138 G139:G148 G149:G155 G156:G164 G165:G173 G174:G181"
  ComboBox3.Clear
  Me.ComboBox3.List = Worksheets("SicRevised").Range(Split(Addr)(ComboBox2.ListIndex)).Value
End Sub[/SIZE]