PeaceJet

証券会社で証券外務員をやりながら、マーケティングやデータ分析・UI/UX改善などを行っています。

VBAで可変長配列を使う方法【エクセルマクロ・VB】

VBAで可変長配列を使う方法【エクセルマクロ・VB

エクセルでマクロを作っているときに、

『特定のメールアドレスだけを、まとめて別のシートへ移す』

などといったニーズがあった場合に可変長配列を使用して解決する方法を、ご紹介いたします。

”Sheet1”には、以下のようなリストが
書かれているとします。

  A B
1 田中 foo@hogehoge.com
2 佐藤 foo@example.com
3 西山 foo@hogehoge.com
4 河原 foo@hogehoge.com
5 foo@example.com

上記リストの中で、example.comだけを抜き出して、

”Sheet1”から”Sheet2”へ貼り付けてみます。

  A B
1 佐藤 foo@example.com
2 foo@example.com

 

Sub VariableArray()

'可変長配列(動的配列)使用を宣言、要素の数はゼロ
ReDim MemberMailAddress(0), MemberName(0)
Dim i, j, n, EndRow As Integer
Dim Domain As String

      
Domain = "@example.com"

With Worksheets("Sheet1")

EndRow = .Range("B65535").End(xlUp).Row

For i = 1 To EndRow

    If InStr(.Cells(i, 2), Domain) > 0 Then

    'UBound関数は、配列の最大の添字を返すので 0 です。
    n = UBound(MemberMailAddress)
    
    '配列の個数を再定義。
    ReDim Preserve MemberName(n + 1)
    ReDim Preserve MemberMailAddress(n + 1)
    
    MemberName(n) = .Cells(i, 1)
    MemberMailAddress(n) = .Cells(i, 2)

    End If

Next i

End With

For j = 0 To UBound(MemberMailAddress)

    With Worksheets("Sheet2")
    
        .Cells(j + 1, 1) = MemberName(j)
        .Cells(j + 1, 2) = MemberMailAddress(j)

    End With

Next j

End Sub