しなやか暮らし研究所パソコン処理 Excelの自動化ソート、フィルター

メニューを消す

フィルター

フィルター設定

Range("A1:Q1").Select
Selection.AutoFilter

フィルターの解除

Set myRange = ActiveSheet.AutoFilter
 
If Not myRange Is Nothing Then                      'フィルターが設定されている
    ActiveSheet.Range("A1").AutoFilter
End If

フィルターが掛かっていたら、全て表示

If ActiveSheet.FilterMode = True Then
    ActiveSheet.ShowAllData
End If

フィルターが絞り込まれている

If ActiveSheet.AutoFilter.FilterMode Then
    MsgBox "絞り込まれています"
Else
    MsgBox "絞り込まれていません"
End If

フィルターの状態確認

Dim myRange As AutoFilter
Set myRange = ActiveSheet.AutoFilter
 
If Not myRange Is Nothing Then
      MsgBox "設定されています"
Else
      MsgBox "設定されていません"
End If

Dim myRange As AutoFilter
Set myRange = ActiveSheet.AutoFilter
 
If ActiveSheet.AutoFilterMode Then
    MsgBox "設定されています"
Else
    MsgBox "設定されていません"
End If

フィルターとソートの連携

ソート処理はフィルターがかかっている場合とかかっていない場合で異なる。フィルターがかかっている状態で、フィルターの考慮のない命令を発すると実行時エラーになる。

フィルター設定なし
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("A2:A14") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("C2:C14") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range("A1:H14")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

フィルター設定在り
    Range("A1:H14").Select
    Range("A8").Activate
    ActiveWorkbook.Worksheets("Sheet1").AutoFilter.Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").AutoFilter.Sort.SortFields.Add Key:=Range _
        ("A2:A14"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    ActiveWorkbook.Worksheets("Sheet1").AutoFilter.Sort.SortFields.Add Key:=Range _
        ("C2:C14"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

フィルターが掛かっている場合、フィルターによる非表示範囲がソート対象から除かれるので、フィルター設定の状態が不明の場合は、ソート前にフィルターを解除しておく必要がある。

Set myRange = ActiveSheet.AutoFilter

If Not myRange Is Nothing Then                      'フィルターが設定されている
    ActiveSheet.Range("A1").AutoFilter
End If

ソート

一重のソート

ActiveWorkbook.Worksheets("Sheet2").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet2").Sort.SortFields.Add Key:=Range("Q2:Q" & MaxRow_S), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Sheet2").Sort
        .SetRange Range("A1:S" & CInt(MaxRow))
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
End With


ソートのオプション
Key1		文字列、Rangeオブジェクトを指定します。
Order1		xlAscending 昇順に並び替える
		xlDescending 降順に並び替える
Key2		2番目に優先される文字列,Rangeオブジェクトを指定します
Order2		xlAscending 昇順に並び替える
		xlDescending 降順に並び替える
Key3		3番目に優先される文字列,Rangeオブジェクトを指定します
Order3		xlAscending 昇順に並び替える
		xlDescending 降順に並び替える
Header		xlGuess 先頭行をタイトル行か自動判定する
		xlNo 先頭行をタイトル行と見なさない
		xlYes 先頭行をタイトル行と見なす
OrderCustom  
MatchCase	TRUE 大文字・小文字を区別する
		FALSE 大文字・小文字を区別しない
Orientation	xlSortRows 上から下へ(行の並び替え)
		xlSortColumn 左から右へ(列の並び替え)
SortMethod	xlPinYin 
		xlStroke

二重のソート

ActiveWorkbook.Worksheets("Sheet2").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet2").Sort.SortFields.Add Key:=Range("Q2:Q" & MaxRow_S), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Sheet2").Sort.SortFields.Add Key:=Range("A2:A" & MaxRow_S), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Sheet2").Sort
        .SetRange Range("A1:S" & CInt(MaxRow))
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
End With

AB列をキーに降順ソート

ActiveWorkbook.Worksheets(入力Tab).Sort.SortFields.Clear
ActiveWorkbook.Worksheets(入力Tab).Sort.SortFields.Add Key:=Range( _
    "AB2:AB" & RowMax), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption :=xlSortNormal
With ActiveWorkbook.Worksheets(入力Tab).Sort
    .SetRange Range("A1:AI" & RowMax)
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With

R列をキーに昇順ソート

ActiveWorkbook.Worksheets(入力Tab).Sort.SortFields.Clear
ActiveWorkbook.Worksheets(入力Tab).Sort.SortFields.Add Key:=Range( _
    "R2:R" & RowMax), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption :=xlSortNormal
With ActiveWorkbook.Worksheets(入力Tab).Sort
    .SetRange Range("A1:AI" & RowMax)
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With

検索

Columns("B:B").Select
Selection.Find(What:="96266723313", After:=Cells(1, 2), LookIn:=xlFormulas _
   , LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
   MatchCase:=False, MatchByte:=False, SearchFormat:=False).Activate

置換

schWhat = 削除集計種類コード(I)
Selection.Replace What:=schWhat, Replacement:="0", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False

検索対象が見つからなかった場合も考慮した一般的な検索

schWhat = "7"
    Set FoundCell = Range("W:W").Find(What:=schWhat)
    If FoundCell Is Nothing Then
        MsgBox "検索に失敗しました"
    Else
        DelStart = FoundCell.Row
    End If

Findメソッドは見つかったセル(Rangeオブジェクト)を返す。対象セルが見つからなかった場合はNothingという特別な状態を返すので、Findメソッドの結果を変数に格納し返り値がNothingかどうか判定する。

R列が101の最初の行を上から検索

Dim schSheet As String                      '検索シート名
Dim schColumns As String                    '検索列
Dim schRg As Range                          '検索範囲
Dim schWhat As String                       '検索文字
Dim fndCell As Range                        '検索したセル
Dim fstRow As Long                          '検索したセルの行
Dim fstColumn As Integer                    '検索したセルの列
 
schSheet = 入力Tab
schColumns = "R:R"
schWhat = "101"
 
'検索範囲をセット
Set schRg = Worksheets(schSheet).Columns(schColumns)
 
'アクティブセルを検索範囲の使っている最後のセルにする
Range("R2").Select
 
'検索開始
Set fndCell = schRg.Find(What:=schWhat, after:=ActiveCell, LookIn:=xlFormulas, _
            LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False, MatchByte:=False)
'見つかった！
If Not (fndCell Is Nothing) Then
    '最初に見つかったセルの行、列番号を記憶
    fstRow = fndCell.Row
    fstColumn = fndCell.Column
End If

Object　　　　　　Rangeオブジェクト
What　　　　　　　検索するデータを指定 [省略不可]
After　　　　　　単一のセルを指定。この引数に指定したセルの次のセルから検索を開始する。省略するとObjectで指定したセル範囲の左上端セルの次のセルから検索する [省略可能]
LookIn　　　　　　検索の対象を指定数式（xlFormulas）、値（xlValues）、コメント（xlComments） [省略可能]
LookAt　　　　　　完全に同一なセルだけを検索（xlWhole）、一部分でも一致するセルの検索（xlPart） [省略可能]
SeachOrder　　　　検索方向を指定する。列方向に検索する（xlByColumns）、行方向に検索する（xlByRows） [省略可能]
SearchDirection　前方に検索(xlNext：既定値)、後方に検索(xlPrevious)[省略可能]
MatchCase　　　　大文字と小文字を区別する(True)、区別しない(False) [省略可能]
MatchByte　　　　半角と全角を区別する(True)、区別しない(False) [省略可能]
SearchFormat　　　書式を検索する (True)、検索しない(False) [省略可能]（※）

W列のアクティブセルから以降を検索

Dim schSheet As String                      '検索シート名
Dim schColumns As String                    '検索列
Dim schRg As Range                          '検索範囲
Dim schWhat As String                       '検索文字
Dim fndCell As Range                        '検索したセル
Dim fstRow As Long                          '検索したセルの行
Dim fstColumn As Integer                    '検索したセルの列
 
schSheet = 入力Tab
schColumns = "W:W"
schWhat = "0"
'検索範囲をセット
Set schRg = Worksheets(schSheet).Columns(schColumns)
 
'検索範囲のアクティブセル以降を検索
Range("W2").Select
 
'検索開始
Set fndCell = schRg.Find(What:=schWhat, after:=ActiveCell, LookIn:=xlFormulas, _
            LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False, MatchByte:=False)
'見つかった！
If Not (fndCell Is Nothing) Then
    '最初に見つかったセルの行、列番号を記憶
    fstRow = fndCell.Row
    fstColumn = fndCell.Column
End If

7 8 9