エクセルVBA ひとつ下の行を選択したい

質問

オートフィルタがかかっている状態で、
ひとつ下の行のセルを選択したいです。

具体的には、エクセルシートで選択したセルの情報をフォームで編集するのですが、
そのフォームの中のスピンボタンで選択しているセルを上下に移動させたいのです。
以下のコードだと、当然ですがフィルタで抽出されたセル以外も選択されてしまいます。エクセルシート上での矢印キーやENTERキー（入力後にセルを移動する方向が下のとき）と同じ動きがしたいです。
どうかお助けください。。。

Private Sub SpinButton1_SpinUp()
Cells(Selection.Row - 1, Selection.Column).Select
Call UserForm_Initialize
End Sub

Private Sub SpinButton1_SpinDown()
Cells(Selection.Row + 1, Selection.Column).Select
Call UserForm_Initialize
End Sub

回答No.3

KenKen_SP · ベストアンサー

こんにちは。

シンプルに ActiveCell から非表示の行までループで調べれば良いのでは？
例えば、こんな感じで。

Private Sub SpinButton1_SpinDown()
　　Call MoveCursor(1)
End Sub

Private Sub SpinButton1_SpinUp()
　　Call MoveCursor(-1)
End Sub

Private Sub MoveCursor(ByVal lMoveCount As Long)
　　If Not TypeOf Selection Is Range Then Exit Sub
　　Dim i As Long
　　i = lMoveCount
　　With ActiveCell
　　　　On Error GoTo Err_
　　　　While .Offset(i).EntireRow.Hidden
　　　　　　i = i + lMoveCount
　　　　Wend
　　　　.Offset(i).Select
　　　　On Error GoTo 0
　　End With
　　Exit Sub
Err_:
End Sub

お礼

ありがとうございました。
大変参考になりました。
非常にシンプルで的を得た手段です。

いろいろ試した結果、簡単に
Do
Cells(Selection.Row - 1, Selection.Column).Select
Loop While Selection.EntireRow.Hidden = True
で非表示行を探す方法を取りました。

KenKen_SP さんのOffsetを使用した方法のほうが
軽快で負担が少ないかと思います。
今後の参考とさせていただきます。
すばらしい回答をありがとうございます。

回答No.4

Wendy02

こんにちは。

以下は、UserForm のモジュールに貼り付けてください。
ただ、ひとつだけ、現在のコードには欠点があります。
UserForm を立ち上げた後に、オートフィルターの設定を替えた時に、その内容を見失ってしまいます。本来、オートフィルター側に、インスタンスの設定を加えなくてはなりません。そのためには、今のコードだけでは足りなくなります。
とりあえず試してみてください。

なお、そのままにしましたが、
Call UserForm_Initialize
というのは、ちょっとヘンです。Initialize は、UserForm を立ち上げたときに設定させるものですから、SpinButton をクリックするたびに、起動するというのは、意味が分かりません。こういうのは、サブルーチンを持ってきたほうがよいです。

'----------------------------------
Dim Rcol As New Collection
Dim i As Long
Dim j As Long

Private Sub SpinButton1_SpinUp()
'上へ
If Rcol.Count = 0 Then Call MakeCollection
　　j = ActiveCell.Column
　　With ActiveSheet
　　　　If .AutoFilterMode Then
　　　　　　i = -SpinButton1.Value
　　　　　　.Cells(Rcol(i).Row, j).Select
　　　　Else
　　　　　　If ActiveCell.Row > 1 Then
　　　　　　　　.Cells(ActiveCell.Row - 1, j).Select
　　　　　　End If
　　　　End If
　　　　'Call UserForm_Initialize
　　End With
End Sub

Private Sub SpinButton1_SpinDown()
'上へ
If Rcol.Count = 0 Then Call MakeCollection
　　j = ActiveCell.Column
　　With ActiveSheet
　　　　If .AutoFilterMode Then
　　　　　　i = -SpinButton1.Value
　　　　　　.Cells(Rcol(i).Row, j).Select
　　　　Else
　　　　　　If ActiveCell.Row < Rows.Count Then
　　　　　　　.Cells(ActiveCell.Row + 1, j).Select
　　　　　　End If
　　　　End If
　　End With

　　'Call UserForm_Initialize

End Sub

Sub MakeCollection()
　　'オートフィルタの内容を取得する
　　Dim c As Range
　　Dim k As Long
　　Dim n As Long
　　Dim m As Long
　　With ActiveSheet
　　　　k = -1
　　　　If .AutoFilterMode Then
　　　　If Rcol.Count > 0 Then Exit Sub
　　　　　　For Each c In .AutoFilter.Range.Columns(1). _
　　　　　　　　SpecialCells(xlCellTypeVisible).Cells
　　　　　　　　Rcol.Add c
　　　　　　　　If ActiveCell.Row = c.Row Then
　　　　　　　　　　n = k
　　　　　　　　End If
　　　　　　　　k = k - 1
　　　　　　Next c
　　　　　　With Me
　　　　　　　　.SpinButton1.Max = -1
　　　　　　　　.SpinButton1.Min = -Rcol.Count
　　　　　　　　If n = 0 Then n = -1
　　　　　　　　.SpinButton1.Value = n
　　　　　　End With
　　　　Else
　　　　　　For m = 1 To Rcol.Count
　　　　　　　Rcol.Remove m
　　　　　　Next m
　　　　End If
　　End With
End Sub

お礼

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

UserForm_Initialize の中で表示項目の初期化などをしているため
そのままCallしましたが、おっしゃるようにプログラムとしては
良くないです。サブルーチン化することにしました。

また、私にはむずかしかったのですが、
spinボタンの使い方の発想がおもしろいと思います。
非常に参考になりました。
まだまだ始めたばかりですが、参考にさせていただきます。

回答No.2

cafe_au_lait

SendKeysステートメントを使用してはいかがでしょうか？

参考URL：

http://officetanaka.net/excel/vba/tips/tips65.htm

お礼

どうもありがとうございました。
今回の件ではうまく動作しませんでした。
SendKeyは知らなかったので今後の参考にします。

回答No.1

taka_s777

End()を使用してみてはどうでしょうか。
Selection.End(xlDown).Select
Selection.End(xlUp).Select
というかんじで。
ただしこれだとオートフィルタの範囲の最下行の場合に、行の最終行(65536行目等)までとんでしまう為、以下のようにしてみました。

Private Sub SpinButton1_SpinUp()
If Selection.End(xlDown).Row = Rows.Count Then
Cells(ActiveSheet.AutoFilter.Range(ActiveSheet.AutoFilter.Range.Count).Row + 1, Selection.Column).Select
Else
Selection.End(xlDown).Select
End If
End Sub

お礼

ありがとうございました。
ひとつずつ移動したいので、動作は違いますが、最終行の選択として大変参考になりました。