VBA基本(いつも同じ動作をするように配慮する。)

さて、前項までで自動記録からコード内容の整理までができました。でも、連番は100行固定です。

これは、前項で整理が完了したコードです。


'***************************************************************************************************
'   一連番号100件                                                   Module1(Module)
'
'   作成者:井上治  URL:https://www.ne.jp/asahi/excel/inoue/ [Excelでお仕事!]
'***************************************************************************************************
'変更日付 Rev  変更履歴内容------------------------------------------------------------------------>
'03/06/23(1.00)新規作成
'***************************************************************************************************
Option Explicit

'***************************************************************************************************
'   ■■■ サンプルマクロ ■■■
'***************************************************************************************************
'* 処理名　：一連番号100件
'* 機能　　：一連番号100件
'---------------------------------------------------------------------------------------------------
'* 返り値　：(なし)
'* 引数　　：(なし)
'---------------------------------------------------------------------------------------------------
'* 作成日　：2003年06月23日
'* 作成者　：井上　治
'* 更新日　：2003年06月23日
'* 更新者　：井上　治
'* 機能説明：A列2行目から100件分の一連番号を生成します。
'* 注意事項：
'***************************************************************************************************
Sub 一連番号100件()
    '-----------------------------------------------------------------------------------------------
    ' A2～A4セルに連番を入力
    Range("A2").Value = 1
    Range("A3").Value = 2
    Range("A4").Value = 3
    ' A2～A4セルを101行まで連続データ作成
    Range("A2:A4").AutoFill Destination:=Range("A2:A101"), Type:=xlFillDefault
End Sub

'------------------------------------------<< End of Source >>--------------------------------------

普通、「連続データ作成」の機能を使って、連番の作成を行なう人は多いと思います。でも、これでは100行分しか連番は作成されません。
思い通りの行数で連番を作成する方法はないのでしょうか。問題は「101」の部分を可変にすることです。

自動記録の応用でこれを実現します。

B列より右方にデータが入っているシートに対して、先ほどの方法でA列に連番を生成させるということにしてみます。
そこでB列のデータがある最終行を取得してみます。
B2セルを選択してCtrl＋↓を押す動作をマクロに記録してみます。


    Range("B2").Select
    Selection.End(xlDown).Select

これを整理すれば、


    Range("B2").End(xlDown).Select

となります。
ですが、この方法では途中に抜け行があるとその手前行を指してしまうので、できれば一番下からサーチする方がベストだと言えるでしょう。 A列が何も登録がない段階で以下のように操作します。
①A2セルを選択
②Ctrl＋↓を押す
③→を押す
④Ctrl＋↑を押す
この動作をマクロに記録してみます。


    Range("A2").Select
    Selection.End(xlDown).Select
    Range("B1048576").Select
    Selection.End(xlUp).Select

このようになりました。
つまり、3行目が「"B1048576"」で固定されている以上、上の2行は不要で、整理すると、


    Range("B1048576").End(xlUp).Select

となります。

さらに、旧.xls形式のことも考慮すると、「1048576」も可変にする必要があるので、


    Range("B" & Rows.Count).End(xlUp).Select

とするべきでしょう。
旧.xls形式の場合は、Rows.Countは「65536」になります。これは利用するExcelのバージョンが新しくても保存形式によって定まります。

これを「一連番号100件」に応用すると

前ページの最後に整理した「一連番号100件」のマクロに直前の動的最終行取得の記述を応用するには「Selectメソッド」を行番号取得の「Rowプロパティ」に入れ替えます。


    lngRow = Range("B" & Rows.Count).End(xlUp).Row

まだ、変数については説明していませんが、変数「lngRow」に最終行番号が格納されるという記述です。

これを「一連番号100件」に応用して「101」の部分をこれに置き換えます。


'***************************************************************************************************
'   一連番号n件                                                     Module1(Module)
'
'   作成者:井上治  URL:https://www.ne.jp/asahi/excel/inoue/ [Excelでお仕事!]
'***************************************************************************************************
'変更日付 Rev  変更履歴内容------------------------------------------------------------------------>
'03/06/23(1.00)新規作成
'***************************************************************************************************
Option Explicit

'***************************************************************************************************
'   ■■■ サンプルマクロ ■■■
'***************************************************************************************************
'* 処理名　：一連番号n件
'* 機能　　：一連番号n件
'---------------------------------------------------------------------------------------------------
'* 返り値　：(なし)
'* 引数　　：(なし)
'---------------------------------------------------------------------------------------------------
'* 作成日　：2003年06月23日
'* 作成者　：井上　治
'* 更新日　：2003年06月23日
'* 更新者　：井上　治
'* 機能説明：A列2行目からデータ最終行まで一連番号を生成します。
'* 注意事項：
'***************************************************************************************************
Sub 一連番号n件()
    '-----------------------------------------------------------------------------------------------
    ' A2～A4セルに連番を入力
    Range("A2").Value = 1
    Range("A3").Value = 2
    Range("A4").Value = 3
    ' A2～A4セルをデータ最終行までまで連続データ作成
    Range("A2:A4").AutoFill _
        Destination:=Range("A2:A" & Range("B" & Rows.Count).End(xlUp).Row), _
        Type:=xlFillDefault
End Sub

'------------------------------------------<< End of Source >>--------------------------------------

これでB列の存在する有効行範囲に行番号が振られます。

別解として．．．．

「Rangeプロパティ」は中にカンマを挟んで左上、右下セルを指定することでそのセル範囲を返すようになっていますから、


'***************************************************************************************************
'   一連番号n件                                                     Module1(Module)
'
'   作成者:井上治  URL:https://www.ne.jp/asahi/excel/inoue/ [Excelでお仕事!]
'***************************************************************************************************
'変更日付 Rev  変更履歴内容------------------------------------------------------------------------>
'03/06/23(1.00)新規作成
'***************************************************************************************************
Option Explicit

'***************************************************************************************************
'   ■■■ サンプルマクロ ■■■
'***************************************************************************************************
'* 処理名　：一連番号n件
'* 機能　　：一連番号n件
'---------------------------------------------------------------------------------------------------
'* 返り値　：(なし)
'* 引数　　：(なし)
'---------------------------------------------------------------------------------------------------
'* 作成日　：2003年06月23日
'* 作成者　：井上　治
'* 更新日　：2003年06月23日
'* 更新者　：井上　治
'* 機能説明：A列2行目からデータ最終行まで一連番号を生成します。
'* 注意事項：
'***************************************************************************************************
Sub 一連番号n件()
    '-----------------------------------------------------------------------------------------------
    ' A2～A4セルに連番を入力
    Range("A2").Value = 1
    Range("A3").Value = 2
    Range("A4").Value = 3
    ' A2～A4セルをデータ最終行まで連続データ作成
    Range("A2:A4").AutoFill _
        Destination:=Range(Range("A2"), Range("B" & Rows.Count).End(xlUp).OffSet(0, -1)), _
        Type:=xlFillDefault
End Sub

'------------------------------------------<< End of Source >>--------------------------------------

でも構わないのですが「OffSetプロパティ」などの学習も必要になります。

計算式で処理する方法も紹介します。

これまでの方法はA列に値で一連番号をセットする方法でしたので、セットしてから行挿入・削除を行なうとズレてしまいます。
後からデータ操作する表の場合は一連番号を行番号からの計算式にしておくと行挿入・削除で自動的にシフトしてくれる利点があります。


'***************************************************************************************************
'   一連番号n件                                                     Module1(Module)
'
'   作成者:井上治  URL:https://www.ne.jp/asahi/excel/inoue/ [Excelでお仕事!]
'***************************************************************************************************
'変更日付 Rev  変更履歴内容------------------------------------------------------------------------>
'03/06/23(1.00)新規作成
'***************************************************************************************************
Option Explicit

'***************************************************************************************************
'   ■■■ サンプルマクロ ■■■
'***************************************************************************************************
'* 処理名　：一連番号n件
'* 機能　　：一連番号n件
'---------------------------------------------------------------------------------------------------
'* 返り値　：(なし)
'* 引数　　：(なし)
'---------------------------------------------------------------------------------------------------
'* 作成日　：2003年06月23日
'* 作成者　：井上　治
'* 更新日　：2003年06月23日
'* 更新者　：井上　治
'* 機能説明：A列2行目からデータ最終行まで一連番号を生成します。
'* 注意事項：
'***************************************************************************************************
Sub 一連番号n件()
    '-----------------------------------------------------------------------------------------------
    ' A2セルからデータ最終行まで一連番号を計算式で生成
    Range(Range("A2"), Range("B" & Rows.Count).End(xlUp).OffSet(0, -1)).FormulaR1C1 = "=ROW()-1"
End Sub

'------------------------------------------<< End of Source >>--------------------------------------

この計算式は「一覧表に「連番」を表示したい。(基本操作)」で説明していたものです。