Excel VBAでエクセル操作をするのによく使う機能まとめ

#はじめに
IE操作については、以下にまとめています。
Excel VBAでIEを操作するのによく使う機能まとめ

エクセルはそんなに知識がなくても自由に編集できるので、まだまだ利用がなくなることはないと思いますが、業務効率化や、自動化を考えるときに、エクセル仕事がネックになったりしますよね。将来的にはエクセルを無くしていく様に業務設計することが大事だと思いますが、あるうちはうまく付き合うことも大事かなと思います。
具体的には、データと帳票をごっちゃにしたエクセルを作らないことが大事かと思います。
それができていれば、基本的には、ルールの決まったルーチンワークは無くすことが可能かと思います。
今回は、VBAでのエクセル操作をするのによく使う機能をまとめてみました。

#条件分岐
条件分岐は、条件によって、処理を分岐させる事です。AならばBの処理をする。CならばDの処理をするみたいな形ですね。関数でも使うので、理解している方も多いと思います。

##IF文
IF文は基本ですね。ElseIFの様に条件を増やしていくことも可能です。AndやOrを用いて、かつ、またはの条件を作ることも可能です。詳しくは、「VBA　IF」等で検索すると、たくさん出てくると思います。

If A = B then
    C = D + 1
Else
    C = D - 1   
End If

##Select文
Select文も条件分岐ですが、主に分岐が多い場合に使用します。分岐が多い場合、IF文よりも読みやすくなると思います。詳しくは、「VBA　Seclect」等で検索すると、たくさん出てくると思います。

Select Case A
   Case 1
       B = 1
   Case 2
       B = 2
   Case Else
       B = 0
End Select

#繰り返し
繰り返しもよく使うことになると思います。エクセル操作だと、全ての行で同じ処理をすることはよくありますよね。

##For文
For文は指定した回数繰り返すための構文です。詳しくは、「VBA　For』等で検索すると、たくさん出てくると思います。

Dim LoopCounter As Long
Dim Endrow As Long
Endrow = 10

For LoopCounter = 1 to Endrow
    ThisWorkbook.Worksheets(1).Cells(LoopCounter,1).Value = 5
Next

##Do文
Do文も繰り返し分ですが、条件を設定して、条件に当てはまるまで繰り返す処理を記述します。詳しくは、「VBA　Do』等で検索すると、たくさん出てくると思います。

Do While A = 10
    B = B + A
    A = A + 1
Loop

#最終行の取得
データベース的に扱うエクセルに対して処理をする場合、１行分の処理を作成し、それを最終行まで繰り返すというのは、よくやるのではないかと思います。

Dim EndRow as Long

EndRow = ThisWorkbook.WorkSheets(1).Cells(1,1).End(xlDown).Row '１行１列目から下方向に最終行の取得

注意としては、途中に空行があると、そこが最終行になってしまいます。ですが、エクセルはデータベース的に扱うか、帳票的に扱うかは分けるべきであり、データベース的に扱うエクセルには、空行を入れない様にすべきかと思います。
空行がある最終行を取得したい場合や、詳しく知りたい方は、「VBA　最終行』等で検索すると、たくさん出てくると思います。

#オートフィルタ
エクセル作業でオートフィルタを使用することはよくあると思いますが、VBAでも、特定の行に対して、処理を行いたい場合などにオートフィルタを活用できます。

##オートフィルタの設定
オートフィルタを指定しているシートの場合、ファイルを開いた状況がどうなっているかを調べて、まずは、全データを表示する様にした方が良いと思います。ファイルを開いた際に既に何かしらフィルタされていた場合等の想定外のエラーを避けることができます。

If ThisWorkbook.Worksheets(1).AutoFilterMode = True Then 'フィルタが設定されてる場合
    If ThisWorkbook.Worksheets(1).FilterMode = True Then 'フィルタで絞りこまれている場合
        ThisWorkbook.Worksheets(1).ShowAllData '全てのデータを表示
    Else
    End IF
Else
    ThisWorkbook.Worksheets(1).rows(1).AutoFilter '1行目にオートフィルタを設定
End if

##オートフィルタで絞りこむ
オートフィルタで実際に絞りこむ際の記述方法です。
・１列目をOKでフィルタ

ThisWorkbook.WorkSheets(1).Range("A1").AutoFilter 1, "OK" '1列目をOKでフィルタ

・2列目をOKかNGでフィルタ　xlOrを使用します

ThisWorkbook.Worksheets(1).Range("A1").AutoFilter 2, "OK", xlOr , "NG" '2列目をOKかNGでフィルタ

・3列目をOK以外でフィルタ　<>を使用します。

ThisWorkbook.Worksheets(1).Range("A1").AutoFilter 3, "<>OK" '3列目をOK以外でフィルタ

以下のリンク先がよくまとまっておりました。
オートフィルタを使い倒す

##オートフィルタで抽出された行を繰り返し
繰り返しに関しては、全ての行に対して繰り返しを実施し、条件分岐によって、特定の行のみに処理を実施するということもできますが、オートフィルタの結果を使って、絞り込んだ行に対してのみ処理を実施するということが可能です。

Dim R as range

With ThisWorkbook.Worksheets(1).Range("A1").Offset(1,0)
    For Each R In .Resize(.rows.count-1).SpecialCells(xlCellTypeVisible).rows
        R.Range("A1").Value = "処理済み"  'A列に対して行う処理　Rがずれるので、毎回1に対して処理を行う
    Next R
End With

#CSVの取り込み
CSVファイルをエクセルに取り込むという作業もよくやるのではないかと思います。
データタブから外部データの取り込み・・といった作業ですが、毎回やってると地味にめんどくさいですよね。

##文字コードUTF-8の場合
文字化けしてしまう場合は以下の方法で実施します。

Dim FilePath as String
Dim strLine As String 'CSV１行分
Dim AllLine As Variant 'CSV1行分を区切って格納する配列
Dim Row As Long

FilePath = "C:¥Users¥Test¥DeskTop¥text.csv"

'ADODB.Streamオブジェクトを生成
Dim adoSt As Object
Set adoSt = CreateObject("ADODB.Stream")

With adoSt
    .Charset = "UTF-8" '文字コードをUTF-8に
    .Open 'Streamをオープン
    .LoadFromFile(FilePath) 'ファイルからStreamにデータを読み込む

    Do Until .EOS　'ファイルの最後まで繰り返す

        strLine = .ReadText(adReadLine) 'Streamから1行読み込み
        AllLine = Split(strLine,",") 'カンマ区切りで配列に格納
        'A1セルを起点として、列数を配列の要素数分広げ、行数のオフセット分ずらして展開する
        ThisWorkBook.Worksheets(1).Range("A1").Resize(1,Ubound(AllLine) + 1).Offset(Row) = AllLine

        Row = Row + 1
    Loop
    .Close

End With

##Line Inputを使う

Dim FilePath as String
Dim strLine As String 'CSV１行分
Dim AllLine As Variant 'CSV1行分を区切って格納する配列
Dim Row As Long
Dim Col as Long

FilePath = "C:¥Users¥Test¥DeskTop¥text.csv"

Open FilePath For Input As #1
    Do Until EOF(1)
        Line Input #1, strLine
        Allline = Split(strLine,",")
        Row = Row + 1
        For col = 0 to Ubound(AllLine)
            thisworkbook.Worksheets(1).Cells(Row,Col + 1) = AllLine(col)
        Next
    Loop
Close #1

#ディクショナリ
複数のエクセルファイル間でデータを突合し、また、突合した結果、値を転記するという作業はよくありますよね。
関数でいうと、VlookUPがよく使われるのではないかと思います。
VlookUPも数百件程度のデータなら問題ないと思いますが、数万件、数十万件レベルになると、とても使用に耐えられなくなってきます。
その場合、参照されるデータをディクショナリに格納してから突合をかけると、高速に処理することが可能です。

ディクショナリの構造は、キーとバリューになりますので、例えば複数の値をバリューにセットしたい場合は、＠、／等の区切り文字をつけてバリューに格納します。
※データ中に必ず使用しない区切り文字である必要があります。

以下、ディクショナリへの登録方法です。

Dim sKey As String
Dim sValue As String
Dim Dic As Object

Dim EndRow As Long
Dim LoopCounter As Long

Set Dic = CreateObject("Scripting.Dictionary") 'ディクショナリオブジェクトをセット

EndRow = ThisWorkbook.WorkSheets(1).Cells(1,1).End(xlDown).Row '１行１列目から下方向に最終行の取得

For LoopCounter = 2 To EndRow

    With ThisWorkbook.Worksheets(1)
        sKey = .Range("A" & LoopCounter).Value
        sValue = .Range("B" & LoopCounter).Value
    End With

    If Dic.exists(sKey) Then '既にキーが存在するか確認

    Else
        Dic.Add sKey,sValue 'ディクショナリに登録
    End If

Next

ディクショナリの値をキーで取り出す場合、以下の様に記述します。

Dim a As String
Dim sKey As string
sKey = ThisWorkbook.Worksheets(1).Range("C1").Value

a = Dic.Item(sKey)

#検索
大量のデータを突合する場合などは、どちらかをディクショナリに格納して処理する方が早いですが、検索と置換の処理等を行いたい場合、Findメソッドを使用できます。

Dim SearchRange As Range
Dim SearchResult As Range
Dim SearchWord As String

Set SearchRange = Range("A1:D10") 'A1〜D10を検索
SearchWord = "テスト" 'テストという文字列を検索
Set SearchResult = SearchRange.Find(SearchWord, LookAt:=xlWhole) '部分一致は、xlPart

'検索がヒットしたかどうかを分岐
If SearchResult Is Nothing Then
    '検索がヒットしなかった場合の処理
Else
    '検索がヒットした場合の処理
    Thisworkbook.Worksheets(1).Range("A1").Value = SearchResult.Row 'ヒットした行番号を記録
End If

'複数ヒットする場合の処理
Dim ResultCell As Range
Dim FirstRow As Long
Dim i As Long
i = 1

Set ResultCell = SearchResult
FirstRow = SearchResult.Row
'検索が最初のセルに戻るまで繰り返し
Do
    Set ResultCell = SearchRange.FindNext(SearchResult)
    Thisworkbook.Worksheets(1).Cells(i,2).value = ResultCell.Row
Loop while ResultCell.Row <> FirstRow

#文字列操作
個々の処理でも、文字列操作を使う場面は結構あるのではないかと思います。
##Right,Left,Mid（文字列の抜き出し）
文字列の抜き出しには、Right,Left,Midを使用します。

Dim A As String
Dim B As String
A = "アイウエオ"

B = Left(A,2) 'アイが入る
B = Right(A,2) 'エオが入る
B = Mid(A,2,2) 'イウが入る

##InStr（文字の位置検索）
文字列の中の特定の文字の位置検索はInStrを使用します。また、検索文字が見つからなかった時は、0が返ってくるので、文字列があるかないかで処理を分けることができます。

Dim A As String
Dim B As String
Dim Result As Long
A = "アイウエオ"
B = "ウ"

Result = InStr(A,B) ' Resultには３が入る

'文字列があるかないかで分岐
if Result > 0 Then
    '文字列があったときの処理
Else
    '文字列がなかったときの処理
End If

##Replace（文字列の置換）
文字列を置換する時は、Replaceを使用します。空白データの削除などによく使用します。
システムから値を取得する場合などで、空白が混じってくる場合があり、そういった場合に使用します。

Dim A As String
Dim B As String

A = " アイウエオ "
B = Replace(Replace(A," ",""),"　","") '半角と全角の空白を削除

#ブック操作
エクセル操作なので、ブック操作もよく使うと思います。
##ブックを開く
個人的には、開いた直後はアクティブになるので、ブック名の取得をすることが多いです。

Dim FilePath As String
Dim BookName As String
FilePath ="ファイルのフルパス"

Workbooks.Open FilePath 'ブックを開く
BookName = ActiveWorkbook.Name 'ブック名を取得

##ブックを閉じる

WorkBooks(BookName).Close

##保存

'上書き保存
WorkBooks(BookName).Save
'名前をつけて保存
WorkBooks(BookName).SaveAs "ファイル名含めたファイルのフルぱす"

##日付の取得
名前をつけて保存をするときには、よくタイムスタンプをつけますが、タイムスタンプはDateで取得できます。同じ日に保存しても重複しないようにするには、時間も含めたNowが活用できます。
年、月、日をそれぞれ取得することもできます。

Dim YYYYMMDD As String
Dim YYYYMMDDHHSS As String
Dim Y As String
Dim M As String
Dim D As String

YYYYMMDD = Date
YYYYMMDDHHSS = Now
YYYY = Year(Date)
MM = Month(Date)
DD = Day(Date）

#フォルダの一覧操作
あるフォルダの中に入れたファイル全部に対して処理を行いたい場合もありますよね。
そんな操作をしたい場合、以下のページが参考になります。

VBAで指定フォルダのファイル一覧を取得

##長くなりそうなので、随時追記していきます