コンボボックス（最終行取得）

マクロ初心者です。（2003使用）
ユーザーフォーム（Sheet2にでます）に、コンボボックス１つとコマンドボックス１つを置きました。コマンドボックスは、Sheet1のC2から最終行までを表示させたいのですが、今わからないのでC2000と大きな数字をいれています。
＊最終行の表示の仕方をどなたか教えていただけませんでしょうか？



[Sheet1]→このデータをもとにしています。
担当課客先管理番号品名注文数量出荷数量
A岡田さん1324りんご3020
B山田さん1554みかん25070
C岡田さん7634なし4025
B金子さん4653みかん7570
A金子さん6675りんご17060
C杉浦さん7789りんご200120
・・・
このような表が３００件くらいあります（日々行数が変動します）


↓こんな感じでやってみて、いちおう動きますが、
２０００件分コンボボックスで出てしまいます。

Private Sub CommandButton1_Click()
Dim lRow As Long
With Worksheets("Sheet2")
lRow = .Range("A" & Rows.Count).End(xlUp).Row
.Range("A" & lRow + 1).Value = ComboBox1.Value
End With
End Sub


Private Sub UserForm_Initialize()
ComboBox1.RowSource = "Sheet1! C2:C2000" ←2000は最終行取得がわからなかったから、大きい数字をいれました。ここを教えてほしいです。
ComboBox1.ListIndex = -1
ComboBox1.SetFocus
End Sub

どなたか教えてください。
よろしくお願いします。

No.1 ベストアンサー 回答者： n-jun 回答日時： 2009/08/20 22:44

ComboBox1.RowSource = "Sheet1! C2:C" & Worksheets("Sheet1").Range("C" & Rows.Count).End(xlUp).Row

とか？

この回答へのお礼

ありがとうございます。助かりました。

お礼日時：2009/08/22 16:29

No.4 回答者： imogasi 回答日時： 2009/08/20 23:42

＞コンボボックス１つとコマンドボックス１

コンボボックス１つとコマンドボタン１つでは。
リンクさせるアイテム列の最終行を知るコードだけの問題だよ。
ＶＢＡの回答にほんとに良く出てくるもの。
＞ユーザーフォームにコンボを貼り付ける場合とシートに貼り付ける場合と在る。シートの場合は
Sub test01()
d = Range("A65536").End(xlUp).Row
Worksheets("sheet1").ComboBox1.ListFillRange = "a1:A" & d
End Sub
本件はフォーム上らしいが、始めに明記すること。
ーー
300行もコンボに出して、使い物になる？？
良く考えて。

No.3 回答者： watabe007 回答日時： 2009/08/20 23:12

こんばんは、参考に

Private Sub UserForm_Initialize()
　　With Worksheets("Sheet1")
　　　　'Sheet1のC2からC列の最終行のアドレスを求めRowSourceに設定
　　　　Me.ComboBox1.RowSource = .Range("C2", .Cells(Rows.Count, "C").End(xlUp)).Address(External:=True)
　　End With
　　ComboBox1.ListIndex = -1
　　ComboBox1.SetFocus
End Sub
Private Sub CommandButton1_Click()
　　Dim lRow As Long
　　With Worksheets("Sheet2").Cells(Rows.Count, "A").End(xlUp)
　　　　'Sheet2のA列の最終行を求め1つ下(Offset(1))のセルにComboBox1の値を転記
　　　　.Offset(1).Value = Me.ComboBox1.Value
　　End With
End Sub

No.2 回答者： M-SOFT#2 回答日時： 2009/08/20 22:47

こんばんは。

コマンドボタンのクリックイベントで最終行の取得を行っているようですが・・・？
あなたが書いたコードではないのですか？
lRow = .Range("A" & Rows.Count).End(xlUp).Row
が最終行の取得です。（この場合はA列です。）
コンボボックスの方はC列なので、C列になるように直せばいいだけです。
後はこの変数を使えばいいので、ComboBox1.RowSource = "Sheet1! C2:C" & lRow　とすればいいでしょう。