【Excel/VBA のこと 08】
Default（標準）では、列番号の表示がアルファベットになっていますが、
数字表記に変更することができます。
VBAでセルを指定するときに、Cells( 1 , 1 ) と記述してマクロを作成すると、
作成途中でセルを変更したり、「For ~ Next」の記述をするときには
数字での記述のほうが便利なのです。
アルファベット⇔数字の切替のマクロを作成して、リボンに登録しましょう。
＿／＿／＿／＿／＿／＿／＿／＿／＿／＿／＿／＿／＿／＿／＿／＿／
列の表示をアルファベットから数字に変更
「R1C1 参照形式を使用する」という項目に
チェックマークを付けると数字に変更できます。
１．「ファイル」タブを開く
２．「オプション」をクリック
３．「数式」をクリック
４．「数式の処理」の中の
「R1C1 参照形式を使用する」のチェックを付ける
５．「OK」をクリック
マクロ
この操作を「マクロの記録」を使って、VBAのコードを取得します。
こちらの記事も参考にしてください。
数字に変更するときは、この一行。
****************************************************************
Sub 列の表示形式を数字に変更
Application.ReferenceStyle = xlR1C1
End
****************************************************************
アルファベットに戻すときは、この一行。
****************************************************************
Sub 列の表示形式をアルファベットに変更
Application.ReferenceStyle = xlA1
End
****************************************************************
Application・・・おまじないとしておこう。
Reference・・・参照
Style・・・形式、スタイル
xlR1C1
xl・・・Excel のこと
R・・・Row 行
C・・・Column 列
※R12C38 = Row の 12 番目、Column の 38 番目 = 12行目、38列目
この2つを合体させて
****************************************************************
Sub 列の表示形式を数字かアルファベットに変更
If Application.ReferenceStyle = xlA1 Then
Application.ReferenceStyle = xlR1C1
else
Application.ReferenceStyle = xlA1
End If
End Sub
****************************************************************
・もし、アルファベット表示だったら数字表示に変更
・もし、アルファベット表示でなければアルファベット表示に変更
ってことです。
あとは、リボンに登録して、いつでも使えるようにしましょう。
こちらを参考にしてください。
こんな感じにしてみました。
最終行の取得
マクロ
表の一番下にデータを追記するときとか、
特定の行にデータがちゃんと入っているかなどを調べるときには
最終行を取得して、表に追記したり、つぎの処理を実行するか判断します。
例えば、列Bの最終行の取得に、以下の記述を使います。
****************************************************
LastRow = Cell(Rows.Count, 2).End(xlUp).Row
****************************************************
Rows・・・行
Count・・・数える
2・・・列Bのこと ※Cells(行番号、列番号）で記載します。
End・・・終わりから
xlUp・・・上に上がる
Row・・・行
つまり、
Rows.Count・・・Excel シートの全部の行を数える
End(xlUp)・・・終わりから上に上がる
なので、
「指定した位置から、上に上がって、
データのあったところの行番号を取得する」
ってことです。（ちょっと強引ですが(汗)）
これは、キーボード操作の
Ctrl + ↑（上矢印キー）と同じことなのです。
****************************************
Selection.End(xlUp).Row
****************************************
と記述すると、
「いま選択されているセルから上に上がってデータのあった行」
となって、最終行を取得できます。
もちろん下から上にではなく、上から下に調べていって、
最終行を取得することもできます。
****************************************
Selection.End(xlDown).Row
****************************************
でも、意外と途中でデータが途切れていて、
最終行を正しく取得できないことがあるのです。
「上から下に」は確実にデータが全部埋まっている場合に使用しています。
Cells(Rows.Count) を使用すると、
Excel のシートの最終行（1,048.576行目）から
上に上がっていけば確実に最終行を取得することができます。
表の最終行に追記する場合は 、
*****************************************
Cell(Rows.Count, 2).End(xlUp).Row + 1
*****************************************
とか、
**************************************************
Cell(Rows.Count, 2).End(xlUp).Offset(1, 0).Row
**************************************************
って、記述すると、最終行の一行下の空欄の行を指定することができるのです。
Offset(行番号、列番号)・・・補正する、埋め合わせをする
なので、
Offset( 1 , 0 ) だと、一行下を指定することができます。
でも、「+1」の方が楽ちんですよね。
（間違いを探すときには、よく見逃すけど・・・）
表の最終行を取得して追記
リストの一番下に追記するには、
**************************************************
Sub 最終行に日付を追記
Dim r as Long
r = Cells(Rows.Count, 2).End(xlUp).Row
Cells( r , 2 ) = "2020年3月”
End Sub
**************************************************
こんな風に、記述できます。
ここまでは、「2列目/ 列B」を例に書いてきましたが、
「列M」の最終行に追記する場合に「列M」がすぐに、
何列目かわかるでしょうか？
「列BW」だった場合は？
そこで、最初に作成した、列の表示を数字にワンクリックで
変更できるマクロが必要だったのです。
おまけ
同じように、最終列を取得する場合は、こちら
列の右から調べる
**************************************************
Cells(1, Columns.Count).End(xlToRight).Column
**************************************************
左から調べる場合
**************************************************
Cells(1, Columns.Count).End(xlToLeft).Column
**************************************************
こちらも、Ctrl + →（←）と同じ動きです。
＿／＿／＿／＿／＿／＿／＿／＿／＿／＿／＿／＿／＿／＿／＿／＿／
