Excelのこととか色々

マクロをどんな Excel ファイルにも実行できるように設定して、3秒かかる操作も1秒で完了。あといろいろ書いてみます・・・

【Excel/VBA のこと08...列の表示を数字に変更/最終行の取得】

【Excel/VBA のこと 08】

f:id:tuna-kichi:20200223230704p:plain

  

Default(標準)では、列番号の表示がアルファベットになっていますが、

数字表記に変更することができます。

 

VBAでセルを指定するときに、Cells( 1 , 1 ) と記述してマクロを作成すると、

作成途中でセルを変更したり、「For ~ Next」の記述をするときには

数字での記述のほうが便利なのです。

 

アルファベット⇔数字の切替のマクロを作成して、リボンに登録しましょう。

 

 

 _/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/

 

列の表示をアルファベットから数字に変更

「R1C1 参照形式を使用する」という項目に

チェックマークを付けると数字に変更できます。

 

1.「ファイル」タブを開く

2.「オプション」をクリック

3.「数式」をクリック

4.「数式の処理」の中の

  「R1C1 参照形式を使用する」のチェックを付ける

5.「OK」をクリック

f:id:tuna-kichi:20200229161103p:plain


マクロ

この操作を「マクロの記録」を使って、VBAのコードを取得します。

 

こちらの記事も参考にしてください。 

www.tuna-kichi.com

 数字に変更するときは、この一行。

****************************************************************

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

****************************************************************

 

・もし、アルファベット表示だったら数字表示に変更

・もし、アルファベット表示でなければアルファベット表示に変更

ってことです。

 

あとは、リボンに登録して、いつでも使えるようにしましょう。

こちらを参考にしてください。

 

 

www.tuna-kichi.com

 

 

こんな感じにしてみました。

f:id:tuna-kichi:20200229174010p:plain

 

f:id:tuna-kichi:20200229175145p:plain



最終行の取得

マクロ

表の一番下にデータを追記するときとか、

特定の行にデータがちゃんと入っているかなどを調べるときには

最終行を取得して、表に追記したり、つぎの処理を実行するか判断します。

 

例えば、列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  + →(←)と同じ動きです。

 _/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/