配列を使って行列を入れ替えるマクロ（Excel VBA）

配列を使ってワークシート上に並んだデータの行列を入れ替えるマクロ（Excel VBA）を作ってみたので紹介します。

特に目的があったわけではないのですが、誰かの何かのお役に立てばと思います ^^

以下のように行列ぎっしりと詰め込んで並んだデータの行列を入れ替えるものとします。

マクロ実行後はこのように ↓ セル A1 を起点に行列が入れ替わります。

一応作ってようと思ったきかけをお話ししておきますと、同じワークシート上で手作業でデータをコピー後、セルA1を選択して「形式を選択して貼り付け」で「行列を入れ替え」ようとすると以下のようなエラーが出てきたからです。

どうも行列を入れ替えて貼り付ける場合は、既にデータがある場所だと上手く行かないようです。

VBA で上記て手作業と同じことをやってみましたが、やはり同じエラーが出てしまいボツになりました（ボツになったコードも一応最後に示しておきます）。

そこで VBA の配列を使って1つずつデータを読み込み、1つずつ置き換えてみることにしました。

この VBA コード単独で使うことは無いと思うのですが、自分自身何かのツールを作っているときに部分的に使いたい場合も出てくるかも知れませんので。

1、配列を使って行列を入れ替える VBA コード
1. 全 VBA コード
2. VBA コード説明
2、ボツになった VBA コード
3、まとめ
編集後記

1、配列を使って行列を入れ替える VBA コード

全 VBA コード

全 VBA コードを以下に示します。

Sub Row_Col_Exchange()
    
    Dim X(1 To 10000, 1 To 10000) As Variant  'セルの値を入れる配列'
    Dim i As Long  '元のデータの行番号'
    Dim j As Long  '元のデータの列番号'
    Dim ii As Long  '貼り付け後のデータの行番号'
    Dim jj As Long  '貼り付け後のデータの列番号'
    Dim Max_Row As Long  '値が入っている最大行番号'
    Dim Max_Col As Long  '値が入っている最大列番号'
    
    Max_Row = Range("A1").End(xlDown).Row        '値が入っている最大行番号'
    Max_Col = Range("A1").End(xlToRight).Column  '値が入っている最大列番号'
    
    '元のデータの読み込み'
    For i = 1 To Max_Row
    
        For j = 1 To Max_Col
            
            X(i, j) = Cells(i, j)
            
        Next j
    
    Next i
    
    
    '元のデータを消去'
    Range(Cells(1, 1), Cells(Max_Row, Max_Col)).ClearContents
    
    
    '元のデータの行列を入れ替えて表示'
    For ii = 1 To Max_Col
    
        For jj = 1 To Max_Row
            
            Cells(ii, jj) = X(jj, ii)
            
        Next jj
    
    Next ii
        
End Sub

Sub Row_Col_Exchange()

Dim X(1 To 10000, 1 To 10000) As Variant 'セルの値を入れる配列'

Dim i As Long '元のデータの行番号'

Dim j As Long '元のデータの列番号'

Dim ii As Long '貼り付け後のデータの行番号'

Dim jj As Long '貼り付け後のデータの列番号'

Dim Max_Row As Long '値が入っている最大行番号'

Dim Max_Col As Long '値が入っている最大列番号'

Max_Row = Range("A1").End(xlDown).Row '値が入っている最大行番号'

Max_Col = Range("A1").End(xlToRight).Column '値が入っている最大列番号'

'元のデータの読み込み'

For i = 1 To Max_Row

For j = 1 To Max_Col

X(i, j) = Cells(i, j)

Next j

Next i

'元のデータを消去'

Range(Cells(1, 1), Cells(Max_Row, Max_Col)).ClearContents

'元のデータの行列を入れ替えて表示'

For ii = 1 To Max_Col

For jj = 1 To Max_Row

Cells(ii, jj) = X(jj, ii)

Next jj

Next ii

End Sub

VBA コード説明

一番発想が必要な部分は For 文の使い方でしょうか。

変数の定義

    Dim X(1 To 10000, 1 To 10000) As Variant  'セルの値を入れる配列'
    Dim i As Long  '元のデータの行番号'
    Dim j As Long  '元のデータの列番号'
    Dim ii As Long  '貼り付け後のデータの行番号'
    Dim jj As Long  '貼り付け後のデータの列番号'
    Dim Max_Row As Long  '値が入っている最大行番号'
    Dim Max_Col As Long  '値が入っている最大列番号'

Dim X(1 To 10000, 1 To 10000) As Variant 'セルの値を入れる配列'

Dim i As Long '元のデータの行番号'

Dim j As Long '元のデータの列番号'

Dim ii As Long '貼り付け後のデータの行番号'

Dim jj As Long '貼り付け後のデータの列番号'

Dim Max_Row As Long '値が入っている最大行番号'

Dim Max_Col As Long '値が入っている最大列番号'

変数 X は2次元配列です。今回、元のデータを読み込むために使っています。

データの数は 10,000 × 10,000 としています。実際にはそんなにデータ数は多くありません。

変数 Xの後の（）内に「1 To 10000」と記述することで、インデックス番号は 1 から 10000 までとなります。

今回の場合、2次元なので要素は X(1, 1) から X(10000, 10000) までとなります。

最大行番号・最大列番号の取得

    Max_Row = Range("A1").End(xlDown).Row        '値が入っている最大行番号'
    Max_Col = Range("A1").End(xlToRight).Column  '値が入っている最大列番号'

1 2	Max_Row = Range("A1").End(xlDown).Row '値が入っている最大行番号' Max_Col = Range("A1").End(xlToRight).Column '値が入っている最大列番号'

元のデータの最大行番号と最大列番号を取得します。

変数 Max_Row には最大行番号を入れています。

Range(“A1”).End(xlDown)

でセルA1 から見て下方向の端のセルを意味します。

そして、

Range(“A1”).End(xlDown).Row

として最後に「.Row」を付けることで上記セルの行番号を意味します。

同様に Max_Col には最大列番号（下図のセルの列番号）を入れます。

元のデータの読み込み

元のデータは For 文を使って配列 X に読み込んでいます。

    '元のデータの読み込み'
    For i = 1 To Max_Row
        For j = 1 To Max_Col
            X(i, j) = Cells(i, j)
        Next j
    Next i

'元のデータの読み込み'

For i = 1 To Max_Row

For j = 1 To Max_Col

X(i, j) = Cells(i, j)

Next j

Next i

配列 X(i, j) のインデックス番号は

i が 1 から Max_Row まで

j が 1 から Max_Col まで

で 1行ずつ読み込んで行きます。

元のデータの値をクリア

ClearContents メソッドを使って元のデータの値を消しておきます。

    '元のデータを消去'
    Range(Cells(1, 1), Cells(Max_Row, Max_Col)).ClearContents

1 2	'元のデータを消去' Range(Cells(1, 1), Cells(Max_Row, Max_Col)).ClearContents

ClearContents メソッドについてはこちらの記事を参考にして下さい。

My タックスノロジー

【初心者向け】Excel VBA（マクロ）でセルに入力した数字・文字列を消す方法

https://my-tax-nology.com/the-way-of-deleting-numbers-and-characters-in-cells

Excel VBA（マクロ）を習い始めたばかりの方の学習を手助けできればと思い、初心者向けの記事を書いています。基本的に Excel VBA（マクロ）では Excel シート上のセルを操作することになります。セルに数字や文字列を書き出すことができますし、逆に書き出した数字や文字列を消すこともできます。今日はセルに書き出した数字や文字列を消す方法についてお話しします。1、セルに書き出した数字や文字列を消したい場合例えば、あるシステムの一部で、シート上に会計ソフトに取り込むための以下のような↓ 並びのデータを表示したいとしま...

元のデータの行列を入れ替えて表示

先に読み込んだ元のデータの行列を入れ替えて表示します。

    '元のデータの行列を入れ替えて表示'
    For ii = 1 To Max_Col
        For jj = 1 To Max_Row
            Cells(ii, jj) = X(jj, ii)
        Next jj
    Next ii

'元のデータの行列を入れ替えて表示'

For ii = 1 To Max_Col

For jj = 1 To Max_Row

Cells(ii, jj) = X(jj, ii)

Next jj

Next ii

読み込みのときと行列反対にしています。

そして、Cells(ii, jj) に先に読み込んだデータを表示していきます。

Cells(ii, jj) = X(jj, ii)

ポイントはインデックス番号 ii, jj を入れ替えているところです。

これによって行列入れ替わって表示されます。

2、ボツになった VBA コード

さて、最初に書きましたボツになったコードを一応書いておきますね。

コピーしてから行列入れ替えて貼り付ける作業を VBA でやったものです。

Sub Row_Col_Exchange1()
    Max_Row = Range("A1").End(xlDown).Row        '値が入っている最大行番号'
    Max_Col = Range("A1").End(xlToRight).Column  '値が入っている最大列番号'
    Range(Cells(1, 1), Cells(Max_Row, Max_Col)).Copy
    Range("A1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
End Sub

Sub Row_Col_Exchange1()

Max_Row = Range("A1").End(xlDown).Row '値が入っている最大行番号'

Max_Col = Range("A1").End(xlToRight).Column '値が入っている最大列番号'

Range(Cells(1, 1), Cells(Max_Row, Max_Col)).Copy

Range("A1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True

End Sub

このコードだとエラーが出てしまいうまく行きませんでした。

3、まとめ

配列を使ってワークシート上に並んだデータの行列を入れ替えるマクロ（Excel VBA）を紹介しました。

for 文を使えば多少のアイデアで色々なことができますね。

編集後記

5月申告の対応に追われています。

金曜の夜には飲みがあり、土曜も予定が入っているのでそれを楽しみに頑張ります。