Code Review Stack Exchange is a question and answer site for peer programmer code reviews. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

How could I make this code shorter and faster?

'Sheets("Summary(FG)") ComboBox1 items
For b = 3 To Sheets("CustomerList").Cells(3, 2).SpecialCells(xlLastCell).row
    If Sheets("CustomerList").Cells(b, 2) <> "" Then
        Worksheets("Summary(FG)").ComboBox1.AddItem (Sheets("CustomerList").Cells(b, 2))
    End If

'Sheets("Summary(RawMat)") ComboBox1 items
For a = 2 To Sheets("RawMatList").Cells(2, 2).SpecialCells(xlLastCell).Column
    If Sheets("RawMatList").Cells(2, a) <> "" Then
        Worksheets("Summary(RawMat)").ComboBox1.AddItem (Sheets("RawMatList").Cells(2, a))
    End If

'Sheets("Summary(WIP)") ComboBox1 items
For c = 3 To Sheets("WIPList").Cells(3, 2).SpecialCells(xlLastCell).row
    If Sheets("WIPList").Cells(c, 2) <> "" Then
        Worksheets("Summary(WIP)").ComboBox1.AddItem (Sheets("WIPList").Cells(c, 2))
    End If

For Each Worksheet In Worksheets
    Application.Goto Reference:=Range("A1"), Scroll:=True
Next Worksheet
share|improve this question
up vote 2 down vote accepted

For a faster version of the code you could add your range to an array and loop through that instead of looping through cells.

For example:

Dim varray as Variant
varray = Sheets("CustomerList").Range("B3:B" & Cells(Rows.Count, "B").End(xlUp).Row).Value

for b = 1 to ubound(varray)
    If varray(b,1) <> "" Then
        Worksheets("Summary(FG)").ComboBox1.AddItem (varray(b,1))
    End If
next b
share|improve this answer

Combobox and Listbox have a List property. You can assign an array to the List property to quickly fill up the control.

The Value property of the Range object returns an array (if the Range is multicell).

That means you can assign the Value property to the List property in one line. You just have to be careful that the column counts match.

Sub FillFinishedGoods()

    Dim rFG As Range

    With Sheets("CustomerList")
        Set rFG = .Range("B3", .Cells(.Rows.Count, 2).End(xlUp))
    End With

    Sheets("Summary(FG)").ComboBox1.List = rFG.Value

End Sub
share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.