【Excel VBA】既存の表からユニーク値分だけ行追加(分かりにくくてすみません)

Excel VBAで、
以下の範囲で5つの項目をうまく操作したいです。
1行目が項目名です。
A列「連番」：2行目からカウント
E列「年月」：「2021年04月」が入っています
F列「区分1」：01～29(29パターンありますが将来増減の可能性あり)
G列「区分2」：A～J(10パターン)
H列「比率」：ある率が入ってます(値は本件と無関係です)

これが以下の様に全組み合わせに対する率が列挙されています。

A列，E列，F列，G列，H列（B～D列は別の情報が入っていて無視してOK）
1，2021年04月，01，A，任意の率
2，2021年04月，01，B，任意の率
・
・
9，2021年04月，01，I，任意の率
10，2021年04月，01，J，任意の率
11，2021年04月，02，A，任意の率
12，2021年04月，02，B，任意の率
・
・
281，2022年04月，29，A，任意の率
282，2022年04月，29，B，任意の率
・
・
290，2022年04月，29，J，任意の率

つまり、AからJまで進んだら、01が02になり、、、の繰り返し、
29，Jまであるので、現在290行あります。
もし区分1が30まで増えたら全行数が300行になるという事になります。

前置きが長くなりましたが、上記は自動で生成されるようになっています。
----------
今回の相談は、その下に自動で追加したいのです。

E列「年月」、F列「区分1」の全パターンに、
G列には「全体」という固定値、
H列には「1」という数値を固定で入れて行追加したいのです。

具体的にはこうなると思います
291，2021年04月，01，全体，1
292，2021年04月，02，全体，1
・
・
319，2021年04月，29，全体，1

つまり今回の場合は290行目の下に29行追加されるのですが、
問題点は、
・区分1の増減により全体の行数が変わる
・A列が連番になっている必要がある

長くなってしまいましたが、問題点解決策のアドバイスをお願いいたします。

質問者からの補足コメント

• 

  2022年というのは誤入力です。失礼いたしました。
  1か月分だけです。
  29行だけ追加されることになりますがその特定方法が分からず。。。

  No.2の回答に寄せられた補足コメントです。 補足日時：2021/03/19 10:22

No.33 回答者： めぐみん_ 回答日時： 2021/03/22 10:29

No.32です。

例えば

基準をセルＡ１～Ａ５としてＢ列のアドレスを .Range("B1") でしゅとくするなら

Dim r As Range

For Each r In Range("A1:A5")

Debug.Print r.Address, r.Range("B1").Address

Next

は

$A$1 $B$1
$A$2 $B$2
$A$3 $B$3
$A$4 $B$4
$A$5 $B$5

こうなります。
同じコードで基準をＢ１～Ｂ５とすると

Dim r As Range

For Each r In Range("B1:B5")

Debug.Print r.Address, r.Range("B1").Address

Next

は

$B$1 $C$1
$B$2 $C$2
$B$3 $C$3
$B$4 $C$4
$B$5 $C$5

のように基準となる r の変化に対して同じ .Range("B1") をつけたしても得られる結果は隣のＣ列に移ります。

この回答へのお礼

何となく分かりますがややこしや～～～
基準をAとすれば、Bは実際のBの事。
基準をBどすれば、Bは実際のCの事。

これが理解できてるから、一見難問なのにいろいろ工夫が思いついて
解決できてしまうんですね。恐れ入りました。

お礼日時：2021/03/23 17:01

No.32 回答者： めぐみん_ 回答日時： 2021/03/22 06:07

＞E1にはデータではなく項目名のセルなのに、何故以降にデータ値がきちんとコピーされるのか

まず with で纏めている点はわかりますでしょうか？
それを抜いたとしてでも良いですけど。

Set r = Range("A:A").SpecialCells(xlCellTypeConstants, 1)
Set r = r.Cells(r.Rows.Count)

最初のコードでＡ列の中の『数値であるセル範囲』を取得します。
次のコードでその範囲から範囲内の行数分に当たるセル（＝最終セル）を再取得します。

そうすると、r (=Ａ列の最終セル) はシート内では Range("A1") として扱うことが出来ます。
すなわち r より上の行・左の列について無視できる状態であれば、r.Range("E1") は r の行においては r からみてセルＥ１に当たります。
例えば r.Range("E2") とするとＥ列で１つ下のセルになる訳です。
なので上や左のセルを指定できないってのはおわかりでしょうか？

元々何もしないシートにおいてセルＡ１は左上に存在し、その上や左にはセルがないですよね？

もし Set r = Range("B:B")~ とＢ列を基準にしてたらＢ列が仮のＡ列になりますので、今回のＥ１は１つ左の r.Range("D1") になるでしょう。
この方法って２０年程前には比較的使われてましたが、１０年程前から使う人も減って（Offsetなどで対応）きているジジィ世代のやり方ですかね。

Offset も良いのですが指定できる範囲であれば数値よりセルアドレスで書けるこちらがわかりやすいかなと（特にＡ列を基準とした時は）自分では思ってて、つい昔の癖で使ってしまいます。

この回答へのお礼

なるほど。最終セルって、シートの一番下からCtrl+↑で飛んで取得する方法しか知りませんでした。勉強になります。
rをRangeとして定義するから、
Set rでセル範囲を格納するって事ですか。
Set関数を理解しないといけないのでしょうが、難しいですね・・・
ありがとうございます。

お礼日時：2021/03/23 16:57

No.31 ベストアンサー 回答者： めぐみん_ 回答日時： 2021/03/21 19:58

No.30です。

もしＥ列がダメなら

With .Range("E1").Resize(n)
.NumberFormat = "@"
.Value = r.Range("E1").Value
End With

セルの書式を文字列にしてから代入してみる。

この回答へのお礼

ありがとうございます。
ご提示のソースに差し替えたらうまく行きました！
セル内が「2020年4月」となっていて理想通りです。
セルの書式が標準のままでも問題ありませんでした。

私は、Range("E1").Resize(n) も勉強せねばありません。
E1にはデータではなく項目名のセルなのに、何故以降にデータ値がきちんとコピーされるのか、等々・・・

いずれにしても、
こちらが無知な分、数々の余計な手間をかけさせてしまったと思います。
お詫びと共に感謝を申し上げます。

お礼日時：2021/03/21 22:26

No.30 回答者： めぐみん_ 回答日時： 2021/03/21 19:27

No.28のお礼について。

No.29のを

With .Range("E1").Resize(n)
.Value = r.Range("E1").Value
End With

でどうでしょう？

Ａ列を値に変えた際にはNo.14でテストして頂いた

Set r = Range("A:A").SpecialCells(xlCellTypeConstants, 1)

こちらに変えてもらえれば可能なはずです。
・セルの値を調べる
・その値は数値である
に当てはまるセル範囲を取得しますので。（前回は数式の有無でしたが）

この回答へのお礼

A列を値に変えたときの対処法まで教えて頂き、ありがとうございます。

また、頂いたソースに差し替えたところ、
先ほどはシリアル値「93922」だったのが、「2020年4月」になりました。
セル自体には「2020/4/1」が入っていて、表示形式で「2020年4月」と表示されるようになっています。

欲を言えば、
文字列として「2020年4月」が入ってくれれば最高なのですが、
これでもほぼ解決です。ありがとうございます。

お礼日時：2021/03/21 20:03

No.29 回答者： めぐみん_ 回答日時： 2021/03/21 15:23

もしかして。

With .Range("E1").Resize(n)
r.Range("E1").Copy .Cells
End With

↓

With .Range("E1").Resize(n)
.Value = r.Range("E1").Value
.NumberFormat = r.Range("E1").NumberFormat
End With

代入の方が良かったのかな？

この回答へのお礼

ありがとうございます。
その部分だけ差し替えてみたら、
E列にもデータが入るようになりましたが、「43922」が入りました。
日付のシリアル値でしょうね。
値が入らない問題点が解決できたことに非常に感謝しております。
(次の回答が既に出ているのでそこで続きを書かせていただきます。)

お礼日時：2021/03/21 19:57

No.28 回答者： めぐみん_ 回答日時： 2021/03/21 13:45

Ｅ～Ｈ列が数式により表示されていると言う場合、その数式が目障りなら先に値に変更って手もなくはないですよ。

⇒Ａ列もいっそ値に変更してしまうって事も。

無論質問文で言うなら連番２９０までですけど。
その後に質問文の処理を行なうとかでも。
Ｂ～Ｄ列については触れない。

この回答へのお礼

アドバイスありがとうございます。

マクロ実行前に、手動でE～H列を値に変更しておいてから、
No.23を実行(もちろん.Value = .Value '★追加 済)してみたら、
希望どうりの結果となりました。
F,G,Hの処理はもちろん、E列の日付もコピーされました。
ただし、元表の最下部セル内は「2020年4月」が、
以降のセルには「2020/4/1」が入っていて、データ形式は異なってますが、表示形式によって見た目が揃っている状態でした。
「2020年4月」という文字で入ってくれると嬉しいです。

ちなみにA列まで値に変更しておいてからマクロ実行するとエラーが出たのは、
「xlCellTypeFormulas」が問題なのかなと推測しただけです・・
ここはA列だけ値にしなければ良いだけですね。

お礼日時：2021/03/21 19:16

No.27 回答者： めぐみん_ 回答日時： 2021/03/21 09:37

＞なのでE列の日付も下に値コピーされると助かります。

一応それを加味してNo.25で

.Value = .Value '★追加

のお願いをしたのですがダメでしたか？

若しくは実行前にＢ～Ｄを削除はされてませんよね？
どのみちＥ列の箇所に上のコードを追加して貰えば、追加分の数式は消えるはずですが、それ以前については参照先が無くなるのでそれを問題にされている訳ではないですよね？
⇒と言うか問題にはなりそうな感じはしますけど。

この回答へのお礼

>.Value = .Value '★追加
もちろん追加しました！
けど駄目でした。
実行前に削除したとか、記載してない事は何もしてません。
続きは、No.28の方に書きます。

お礼日時：2021/03/21 14:19

No.26 回答者： めぐみん_ 回答日時： 2021/03/21 06:30

＞確かに一瞬ですが追加29行のF列に「2021年04月」が表示されたと

ん？
年月はＥ列ではないのですか？
Ｆ列はユニークキーでしたよね？

なんか記載されている情報と実際のシートの項目列にずれがありませんか？
あと仮にＥ列全体が元々年月を数式で得ているとしたら、それを値に変更する必要があるのかどうかも関係するのかな？

この回答へのお礼

そうです。
ご認識の通り、E列は年月、F列はユニークキー(区分1)です。すみません。
記載させている情報と実際のシートにずれはありません。ボケてただけです(笑

No.25でのお礼の文中でF列と書いてしまったのは正しくはE列です。

実は、B,C,D列内の情報を関数で加工して、E,F,G,H列に反映させてます。
最終的にA,E,F,G,H列だけが必要な情報ですが、全て関数が入ってます。
今回、最下行には値で追加したいのです。

(マクロの最後では、B,C,D列を削除して、全シートを値にしてxlsxとして別名保存する予定です。)

なのでE列の日付も下に値コピーされると助かります。

お礼日時：2021/03/21 09:17

No.25 回答者： めぐみん_ 回答日時： 2021/03/20 20:02

No.24です。

あれ？
Ｅ列は文字列かシリアル値の表示変化か不明でしたので、

With .Range("E1").Resize(n)
r.Range("E1").Copy .Cells
End With

と既にコピーにしてますよ？
実行前の最終行の値をコピペします。
もしかして追加する行内で値を変化させたいって事ではないですよね？
もし元々が数式であったなら、


With .Range("E1").Resize(n)
r.Range("E1").Copy .Cells
.Value = .Value '★追加
End With

で如何でしょう？

この回答へのお礼

あれ、おかしいですね。
確かに一瞬ですが追加29行のF列に「2021年04月」が表示されたと思ったら、
すぐに空白になってしまいました。ほんと一瞬でコンマ1秒くらいの瞬間です。
マクロ実行前のF列最下行には数式が入っているので、
追加するときには直前のを値で入れたいのですが、セルの中はnullでした。
E1をE2にしても駄目でした。
すみません。

お礼日時：2021/03/20 22:57

No.24 回答者： めぐみん_ 回答日時： 2021/03/20 01:09

No.23です。

---------
データ数は、区分1(現在29パターン)と区分2(10パターン)の全組合せなので
現在は29×10＝290行存在します。
区分1が30に増えた場合は、30×10＝300行になるため、
301行目から
----------

とありまして、こちらは区分１の増減（１０の倍数）のみ想定しているのですが、区分２も増減する可能性はあるのでしょうか？
もしなければNo.23でいけると思ってはいます。

この回答へのお礼

ありがとうございます。
区分2は増減しない予定なので大丈夫です。

区分1を増やしたり減らしたり試してみました。
バッチリです。
E列もコピーしたいのですがどうすれば良いでしょうか。
常に「2020年04月」ではなく変化しますので、
現在入っている内容を数式ではなく値で入ると完璧です。

お礼日時：2021/03/20 17:47