VBA（エクセルマクロ）でよく使うコードまとめ

概要

実際の開発でよく使っているコードをまとめました。
セル、行、列の指定などの基本的なものから、ブック、シートのオブジェクト取得方法など幅広く書いています。

ショートコード集では「フォルダの中のファイルを1つずつ開いて順次処理する」など
実際の業務でよく使われる処理をコピペで利用できるよう、汎用性の高いコードでまとめています。

VBAは1つの動作に様々な記述方法がありますが、本記事では
1.ハードコーディング(直書き)、ソフトコーディング(変数利用)どちらにも利用しやすい記述方法に絞る
2.省略可能なパラメーターは記載しない

を意識していますので、可能な限り下記の方法で記述をまとめると、統一され分かりやすいコードになると思います。

※注意
・見やすくするためシートオブジェクトの指定をしていません。
指定しない場合アクティブなシートに処理が行われます｡
実際に利用する場合は誤動作防止の為、シートを指定するようにしてください。

・Selectionオブジェクト、selectメソッドで記述しています。そのままSelectionオブジェクト、selectメソッドで利用する場合は、選択対象のあるシートがアクティブである必要があります。

セル、行、列の指定

セルの指定（単一セル、範囲）

'単一セルの指定

'Range方式
Range("A1").Select

'Cells方式 Cells(行, 列)
Cells(1, "A").Select '列を「英字」で指定
Cells(1, 1).Select   '列を「数値」で指定

'範囲での指定

'Range方式 Range(始点セル,終点セル)
Range("A1", "C5").Select

'Cell方式では範囲指定出来ないため行・列を「数値」で指定したい場合Rangeと組み合わせる
Range(Cells(1, 1), Cells(5, 3)).Select

行の指定（単一行、複数行）

'単一行の指定

Rows(1).Select

'複数行の範囲指定

Rows("1:5").Select

列の指定（単一列、複数列）

'単一列の指定

Columns("A").Select '「英字」で指定
Columns(1).Select   '「数値」で指定

'複数列の範囲指定

Columns("A:C").Select

よく使うメソッド

コピー (極力つかわない)

セルの値のみを扱いたい場合は、コピーメソッドは極力使わず、下記の「値のみコピペ」か「配列」を利用する。

'コピー

Selection.Copy

値のみコピペ

'コピペ

'単一セル_値のみコピペ
Range("B1").Value = Range("A1").Value

'範囲の値のみコピペ_コピー元と同じサイズを指定してコピペ
Range("D1", "F3").Value = Range("A1", "C3").Value


'範囲のコピペ_貼り付け先でサイズを気にせず貼り付けたい場合
Dim copyRange As Range  'コピーしたい範囲
Dim pasteRange As Range '貼り付けしたいセル

Set copyRange = Sheets("sheet1").Range("A1", "C3")
Set pasteRange = Sheets("sheet2").Range("B1")
'コピー範囲のサイズを判定して、貼り付けサイズを自動設定して貼り付け
pasteRange.Resize(copyRange.Rows.Count, copyRange.Columns.Count).Value = copyRange.Value


'範囲のコピペ_貼り付け先でサイズを気にせず貼り付けたい場合_簡易版
Sheets("sheet1").Range("A1", "C3").Copy
Sheets("sheet2").Range("B1").PasteSpecial xlPasteValues
'選択範囲の点滅表示をなしに
Application.CutCopyMode = False

切り取り

'切り取り

Selection.Cut

挿入

'挿入

'切り取り後の場合は､切り取った範囲を挿入
'挿入後にセルが移動する方向を 「Shift:=」 で指定する必要あり
Selection.Insert Shift:=xlDown    '下方向へ移動
Selection.Insert Shift:=xlToRight '右方向へ移動

'行・列の挿入の場合､自動的に下・右方向へ移動するので「Shift:=」は不要
Rows(3).Insert       '行の挿入
Columns("D").Insert  '列の挿入

削除

'削除

'削除後にセルが移動する方向を 「Shift:=」 で指定する必要あり
Selection.Delete Shift:=xlUp      '上方向に移動
Selection.Delete Shift:=xlToLeft  '左方向に移動

'行・列の削除の場合､自動的に上・左方向へ移動するので「Shift:=」は不要
Rows(5).Delete          '行の削除
Columns("F").Delete     '列の削除


'確認メッセージが表示される場合があるので、表示させない設定をしてから消す
Application.DisplayAlerts = False 'メッセージを非表示設定に変更
Range("A2", "K2000").Delete Shift:=xlUp
Application.DisplayAlerts = True  'メッセージを表示設定に変更

表に対する処理

1行目が項目行、2行目からデータが始まっている表に対して処理を行うことを想定したコードです。
※途中に空白行などがあるデータだと正しく動作しません。

フィルター

.AutoFilter の後の「数値」でフィルター対象の列を指定(英字での指定は出来ない)
例)「A1」から始まっている表の「C」列目を「鈴木」のみでフィルター
Range("A1").AutoFilter 3, "=" & "鈴木"

'フィルター

'フィルタークリア (フィルターが存在していない場合フィルター準備)
Range("A1").AutoFilter


'フィルター_値に等しい
Range("A1").AutoFilter 1, "=" & "検索文字列"

'フィルター_値に等しくない
Range("A1").AutoFilter 1, "<>" & "検索文字列"

'フィルター_値で始まる
Range("A1").AutoFilter 1, "=" & "検索文字列" & "*"

'フィルター_値で始まらない
Range("A1").AutoFilter 1, "<>" & "検索文字列" & "*"

'フィルター_値で終わる
Range("A1").AutoFilter 1, "=*" & "検索文字列"

'フィルター_値で終わらない
Range("A1").AutoFilter 1, "<>*" & "検索文字列"

'フィルター_値を含む
Range("A1").AutoFilter 1, "=*" & "検索文字列" & "*"

'フィルター_値を含まない
Range("A1").AutoFilter 1, "<>*" & "検索文字列" & "*"

'フィルター_値より大きい
Range("A1").AutoFilter 1, ">" & 123

'フィルター_値以上
Range("A1").AutoFilter 1, ">=" & 123

'フィルター_値より小さい
Range("A1").AutoFilter 1, "<" & 123

'フィルター_値以下
Range("A1").AutoFilter 1, "<=" & 123

'フィルター_条件をどちらも満たす
Range("A1").AutoFilter 1, ">=" & 123, xlAnd, "<" & 130

'フィルター_条件のどちらかを満たす
Range("A1").AutoFilter 1, ">=" & 130, xlOr, "<" & 123

表の選択(全選択、項目行を除いた2行目以降を全選択)

xlsxファイルではなく、CSVデータなど表として定義されていないデータにも対応出来る記述にしています。

'表の全選択

'Ctrl+Aでの全選択と同じ
Range("A1").CurrentRegion.Select

'項目行を除いた2行目以降を全選択

'現在表示されているデータ件数を確認、項目行のみならずデータも存在する場合のみ実行
'表を全選択後、選択範囲を一つ下にずらし、最終行を削る
Range("A1").Select '表の開始セル
If WorksheetFunction.Subtotal(3, Columns(Selection.Column)) > 1 Then
    Selection.CurrentRegion.Select
    Selection.Offset(1, 0).Resize(Selection.Rows.Count - 1).Select
End If

ブック・シートオブジェクト

ブックオブジェクトの取得

'ブックオブジェクトの取得

'マクロを実行しているブックのオブジェクト取得
Dim wb As Workbook
Set wb = ThisWorkbook

'アクティブなブックのオブジェクト取得
Dim wb As Workbook
Set wb = ActiveWorkBook

'ブックを開いてオブジェクト取得
Dim wb As Workbook
Set wb = Workbooks.Open("C:\Users\User\Documents\test.xlsx")

シートオブジェクトの取得

'シートオブジェクトの取得

'シートオブジェクト取得(アクティブなシート)
Dim ws As Worksheet
Set ws = ThisWorkbook.ActiveSheet

'シートオブジェクト取得(シート名指定)
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("sheet1")

'シートオブジェクト取得(一番左のシート)
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets(1)

ブックの保存・閉じる

保存

別名保存の際
・ファイルパスを指定しないと、保存元のブックと同じフォルダに保存される。
・ファイル名に拡張子を記載しないと、保存元のブックと同じ拡張子で保存される。
例1)「test.xlsm」で ThisWorkbook.SaveAs "保存名"　を実行すると
「test.xlsm」があるフォルダに「保存名.xlsm」という名前で保存され、「test.xlsm」は上書き保存されずに閉じる。
例2)ThisWorkbook.SaveAs "C:\Users\User\Documents\test"　を実行すると
「Documents」フォルダに「test.xlsm」という名前で保存される。

保存

'上書き保存
ThisWorkbook.Save

'名前をつけて保存(実行しているブックのあるフォルダと同じ場所に保存)
ThisWorkbook.SaveAs "保存名"

'名前をつけて保存(パスを指定して保存)
ThisWorkbook.SaveAs "C:\Users\User\Documents\test.xlsm"

'名前をつけて保存(同じファイル名が存在する場合も強制的に上書き)
Application.DisplayAlerts = False 'メッセージを非表示設定に変更
ThisWorkbook.SaveAs "C:\Users\User\Documents\test.xlsm"
Application.DisplayAlerts = True  'メッセージを表示設定に変更

閉じる

ブックを閉じる

'ブックを上書き保存して閉じる
ThisWorkbook.Close SaveChanges:=True

'ブックを保存しないで閉じる
ThisWorkbook.Close SaveChanges:=False

ショートコード集

データの入っている最終行・列数を取得

'データの入っている最終行を取得

Dim LastRow As Long
LastRow = Cells(Rows.Count, "A").End(xlUp).Row

'データの入っている最終列を取得

Dim LastCol As Long
LastCol = Cells(1, Columns.Count).End(xlToLeft).Column

データの入っている最終セルの次のセルを選択

'データの入っている最下セルの下のセルを選択

Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Select

'データの入っている最右セルの右のセルを選択

Cells(1, Columns.Count).End(xlToLeft).Offset(0,1).Select

フォルダ内すべてのファイルを順に開き処理を行う

'フォルダ内すべてのファイルを順に開き処理を行う

Dim folderPath As String
Dim fso As Object
Dim files As Object
Dim file As Object
Dim wb As Workbook

'処理対象フォルダパス設定
folderPath = "C:\Users\User\Documents\TestFolder"

'フォルダ内のすべてのファイルを取得
Set fso = CreateObject("Scripting.FileSystemObject")
Set files = fso.GetFolder(folderPath).files

'ファイルの個数分繰り返し
For Each file In files
    Set wb = Workbooks.Open(file)
'    ☆☆☆☆　ここに処理を書く　☆☆☆☆
Next file

すべてのシートに同じ処理を行う

'すべてのシートに同じ処理を行う

Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
'    ☆☆☆☆　ここに処理を書く　☆☆☆☆
Next

シート内のピボットテーブルを更新

'シート内のピボットテーブルを更新

Dim pvt As PivotTable
For Each pvt In ActiveSheet.PivotTables
    pvt.PivotCache.Refresh
Next

列番号を「A1」形式から「R1C1」形式に変換

'列番号を「A1」形式から「R1C1」形式に変換

Dim col_A1Format As String
Dim col_R1C1Format As Long

'例)A→1, C→3
col_A1Format = "C"
col_R1C1Format = Cells(1, col_A1Format).Column

確認メッセージを表示させない

'確認メッセージを表示させない

Application.DisplayAlerts = False 'メッセージを非表示設定に変更
'☆☆☆☆　ここに処理を書く　☆☆☆☆
Application.DisplayAlerts = True  'メッセージを表示設定に変更

コピー・カット時の選択範囲点滅表示をなしに

'選択範囲の点滅表示をなしに

Application.CutCopyMode = False