Take the 2-minute tour ×
Code Review Stack Exchange is a question and answer site for peer programmer code reviews. It's 100% free, no registration required.

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))
    Else
    End If
Next

'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
Next

'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
Next

For Each Worksheet In Worksheets
    Application.Goto Reference:=Range("A1"), Scroll:=True
Next Worksheet
share|improve this question
add comment

2 Answers

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
add comment

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
add comment

Your Answer

 
discard

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.