Excel 任意の行数・列数のデータを合体VBA

質問

VBAで、Sheet1のデータの最終行に
Sheet2の同じ並びのデータを続けてくっつけ、
Sheet3に（Sheet1とSheet2のデータが）連続した状態をつくる場合のVBAの書き方を教えてください。

ちょうどSheet1のブルーの範囲（ただし最終行はn行＝データは様々で最終行は不特定だが最終行の下にはもうデータはない、最終m列の右にはまだデータが膨大にあるが、今回はA～m列（ここではA～AX列と指定可能）をコピーしたい）
に
Sheet2の黄色い範囲（ただし最終行はn行＝データそれぞれで不特定だが最終行の下にはもうデータはない、最終m列はSheet1と同じ構成＝A～AX列）
をくっつけたい。

・アウトプットはSheet3。
（Sheet1とSheet2はいじらずそのまま残す）
・コピーして貼り付ける（複写する）のは、値だけ
（罫線とか計算式は不要）
です。

よろしくお願いします。

回答No.6

kagakusuki · ベストアンサー

>1| With Sheets("Sheet1")
>2|Sheets("Sheet1").Range("A1:AX" & .Range("A" & RowS.Count).End(xlUp).Row).Copy
>3|End With

>の部分ですが、「With｜」で始まり、「End With」で終わる（くくりが終わる）という見方で理解しているのですが、

>Q1)この3行の真ん中の行（2行目）だけでは動作しない
>（やりたいことが起こらない）理由が理解できません。

　まず

.Range("A" & RowS.Count).End(xlUp).Row

の部分の頭には「.」が付いていますが、その更に前には

Sheets("Sheet1")

というシートを指定する構文が付いていない事に注意して下さい。
　これはWithの中で

Sheets("Sheet1")

という構文が既に指定されているので、

Sheets("Sheet1").Range("A" & RowS.Count).End(xlUp).Row

という構文を記述する場合、

Sheets("Sheet1")

の部分を省略して記述する事が出来る様になっているからです。
　ここで、

With Sheets("Sheet1")

と

End With

を記述せずに、

Sheets("Sheet1").Range("A1:AX" & .Range("A" & RowS.Count).End(xlUp).Row).Copy

だけにしてしまいますと、

.Range("A" & RowS.Count).End(xlUp).Row

の部分でシートを指定していない事になりますから、セル範囲を指定した事にはならないためエラーとなってしまいます。
　それならば

Sheets("Sheet1").Range("A1:AX" & Range("A" & RowS.Count).End(xlUp).Row).Copy

の様に

Range("A" & RowS.Count).End(xlUp).Row

の頭に「.」を付けなければ良いかと言いますと、そうは行きません。
　Rangeの前に「.」を付けなければ確かにエラーとはなりませんが、その場合

Sheets("Sheet1").Range("A" & RowS.Count).End(xlUp).Row

という意味にはならず、

Activesheet.Range("A" & RowS.Count).End(xlUp).Row

という意味になってしまいますので、Sheet1以外のシート上でマクロを起動させた際に、その時開いていたシートのA列の最終行の行番号を返す事になり、Sheet1のA列の最終行の行番号を求めた事にはなりません。
　ですから、

.Range("A" & RowS.Count).End(xlUp).Row

という記述の仕方をする場合には、

With Sheets("Sheet1")

と

End With

が必要になる訳です。
　尚、With～End With　は単に構文の記述を省略する事が出来る様にしているだけですから、

Sheets("Sheet1").Range("A1:AX" & Sheets("Sheet1").Range("A" & RowS.Count).End(xlUp).Row).Copy

という記述の仕方をすれば1行だけで済ます事も出来ますが、

Sheets("Sheet1")

という記述が2回も出て来るのは煩雑なため、Withを使って省略した記述の仕方をしている訳です。
　省略した記述の仕方をするというのであれば、

With Sheets("Sheet1")
.Range("A1:AX" & .Range("A" & RowS.Count).End(xlUp).Row).Copy
End With

という記述にすべき所であり、実際、当初はそうしていたのですが、他の所で生じたバグを潰すために構文のあちこちをいじっていた際に、一旦、Sheets("Sheet1")を付けた形で記述した事があり、それを後で元に戻すのを忘れておりました。

>Q2)xlPasteValuesAndNumberFormats」とValueだけでなく、NumberFormatsと宣言してるのはなぜでしょうか。数字も値（Value）は数字そのものとして処理されると考えていましたので、理由が知りたいと思いました（データに日付は出現しません）

　そう申されましても、御質問内容には「・・・・」で表現されている箇所もあり、そこにどんな表示形式が使われているのかといった事に関しては何も説明が御座いませんでしたので、「日付」以外にも「時刻」や「郵便番号」、「電話番号」、「￥マーク付きの金額」等が含まれている可能性も考えられましたし、例え単なる数値だけであったとしましても、「3桁ごとに『,』を付ける」、「表示される小数点以下の桁数に指定がある」、「負の数を赤文字で表示する」、「負の数を▲付きで表示する」、「指数表示にする」等々の様々なパターンも考えられましたので、トラブルや二度手間の元となる恐れを避けるために値と表示形式が反映される様にして居ります。

>Q3) PasteSpecial Paste:=xlPasteValues
>のところで、イコールだけのPaste=xlPasteValues
>ではなく、VBAが「:=」という記述の型を使っている理由をお知りでしたら教えてください。

　私自身はその事に対して何の不満御座いませんでしたので、これまでその様な仕様となっている理由を確認した事は御座いません。
　只、

.Paste Destination:=Range(セルAddress)

という構文もある以上、単純にPaste=xlPasteValuesとしてしまったのでは、PasteSpecialのパラメーターなのか、それともPasteメソッドの書き間違えなのか判り難いと思います。
　私は「:」を付けた方がパラメーターである事が判りやすくて良いと思っております。

お礼

よい記述をありがとうございます。
アマゾンで買う分厚い本だと、コードばかりで文法の解説がなく、今日大きな書店に立ち寄ったら、ステートメントの記述に関する本があり、回答者さんの説明と合わせて、効率をよくするやり方の、Withステートメントであることがようやくわかりました。

また、あとにつづく「.Range」のような「.」で始まる行の意味もわかりました。
大変勉強になりました。

ちなみにわたしはマクロをマスターするポジションではないのですが、マクロやプログラミングができる人を外部（外注）に頼っており、契約にない処理だとなんでもかんでも頼めず、また下請法のしばりもあるため、わたしの業務で必要な処理だけ自分でできるよう、ここで勉強させていただいています。

どうもありがとうございました。

補足

仕事から帰って読みながら研究中です。
理解を深めますので、しばしお待ちください。
ご丁寧な対応ありがとうございます。

回答No.5

keithin

結局２つとも同じ質問なので、一緒に回答します。

ｒ１はたとえばその次のステップで

’その範囲を転記する
　worksheets("Sheet3").range("A1:AX" & r1).value _
　= worksheets("Sheet1").range("A1:AX" & r1).value

Ａ１からＡＸ列のｒ１行のセルまでのセル範囲を取得するために利用しています。
つまりこの構文では、変数ｒ１には「行番号」という数字が入ってなきゃならないワケです。

なので
マクロ：
r1 = worksheets("Sheet1").range("A65536").end(xlup).row

意味：
変数ｒ１に代入しなさい　＝
ワークシート（シート１）の．Ａ６５５３６セルの．そこから上にジャンプした先のセルの．行番号を。

勿論あなたが希望するなら

　dim h as range
’上向きジャンプした先のセルを取得する
　set h = worksheets("Sheet1").range("A65536").end(xlup)

’それを使って転記する
　worksheets("Sheet3").range("A1:AX" & h.row).value _
　= worksheets("Sheet1").range(worksheets("Sheet1").range("A1"), h).value

みたいなこともやればできますが、明らかに回りくどいというかメンドクサイ事をしてます。

＞細かい点ですが説明いただけたらと思います。

今はアナタがご質問された、「これこれの転記を解決する」ためのご相談です。
あなたのエクセルＶＢＡのなんでも相談室じゃありませんし、回答者もいつまでもいつまでもダラダラ追加ご質問にお付き合いする義理はありません。
元のご相談が解決したらこのご相談は解決で閉じて、新しい疑問点は詳しい状況を添えて、新しいご質問として別途投稿し直して下さい。

お礼

解決していないのでお伺いしたのですが、ご迷惑のようですし、義理ももちろんありませんのでもうお聞きしません。
ありがとうございました。

回答No.4

keithin

sub macro1()
　dim r1 as long
　dim r2 as long

’Ａ列を使ってシート１の最終行を取得する
　r1 = worksheets("Sheet1").range("A65536").end(xlup).row
’その範囲を転記する
　worksheets("Sheet3").range("A1:AX" & r1).value _
　= worksheets("Sheet1").range("A1:AX" & r1).value

’Ａ列を使ってシート２の最終行を取得する
　r2 = worksheets("Sheet2").range("A65536").end(xlup).row
’その範囲を、既に貼り付けた下に転記する
　worksheets("Sheet3").range("A2:AX" & r2).offset(r1 - 1, 0).value _
　= worksheets("Sheet2").range("A2:AX" & r2).value
end sub

再掲：
＞転記したい具体的なセル範囲を取得（指定）して、どんどんと転記する

アナタがご自分でヤリタイと仰っていた事は何でしたか？

＞Sheet1のブルーの範囲（ただし最終行はn行＝データは様々で最終行は不特定…をコピーしたい

シート１の対象範囲は具体的に、Ａ１セルから、横はＡＸ列まで、そして下は使用してる最終行まで、ですね。
なので「使用している最終行」が、いまマクロを実行したこの瞬間に具体的に何行まであるのか、２００行とか１０００行まであるとか、調べてその範囲をコピー（転記）すればイイ訳です。

じゃぁ具体的にどうやって「（シート１やシート２に）いま何行までデータが記入されている」のか調べれば良いかと言えば、やり方は沢山たくさんありますが、たとえば

方法１：Ａ１セルからＣｔｒｌ＋↓で下向けにジャンプして、辿り着いた行まである
方法２：Ａ列のずっと下のセル（例えばＡ６５５３６セル）からＣｔｒｌ＋↑で上向けにジャンプして、辿り着いた行まである

といった手順が思いつきます。
それぞれに「失敗するリスク」もあるので、あなたの実際のエクセルの状況に照らしてもっともリスクの小さい方法を選び、最終行を取得します。今回はこの中から、一般に失敗の少ない方法２を採用したという事です。

＃方法１が失敗するケース
　●たまたま１行目しかデータが記入されていない
　●リストの途中に空白セルが紛れている
　●他の列とは行数が違う

＃方法２が失敗するケース
　●たまたまシートの一番下のセルまでみっちりデータが埋まっている
　●リストの下端より下に、ゴミデータが実は紛れていてＣｔｒｌ＋↑がそこで止まる
　●他の列とは行数が違う
　●最終行より上のセルからジャンプすると失敗する

＃他の方法の例（同様にそれぞれ異なる失敗のリスクがあります）
　○Ａ列じゃなくより信頼できる列を使い、同様にＣｔｒｌ＋ジャンプで最終行を取得する
　○シートの最終セル（Ｃｔｒｌ＋Ｅｎｄでジャンプするセル）を使う
　○セルのジャンプじゃなく、たとえばCOUNTA(A:A)といった関数で計算し行数を取得する
　○Ａ列だけじゃなく考えられる全部の列について、イチイチＣｔｒｌ＋ジャンプで最終行を調べて廻り最大値を探す
　etc,etc

お礼

なるほど理由がわかりました。
はい、別質問であったように、A列は空行がある可能性があるため、A列がひっかかっていました。

補足

やりたいデータ処理はできるようになったのですが、理解のために時間がありましたら、次の行の部分の解説をお願いします。

r1 = worksheets("Sheet1").range("A65536").end(xlup).row

この最後の「.end(xlup).row」で、end(xlup)でCtrl+↑の動作に.rowがついている意味（とうかVBA上の概念）を、細かい点ですが説明いただけたらと思います。

まず現状認識をお伝えすると、手持ちの資料には、
「___A__.__B__」
のような記述の仕方（文型）があるとき、
「対象.命令」
「対象.様子」
（のように捉えることにしましょう＝理解の方法として）とあり、
「＝」で「代入」
とあります。

そこから考えるとき、この1行（r1 = worksheet～～）の記述にはドットが複数つながっていて
「worksheets("Sheet1").range("A65536").」は、シート1のA65536の位置（レンジ）
というのはわかるのすが、そのあとあに.end(xlup).row
と続けているその概念（1行の文型の捉え方）がつかみ切れていません。

Q1)これは（VBAの文法としては）「.」で状況（上でいう「対象.様子」）をまず先に並べ、そのあとにドットをつづけ、そこにやりたいことを記述するという概念で捉えておけばいいのでしょうか。

Q2)また、「.end(xlup)」だけでなく「row」がつくのはなぜでしょうか「.end(xlup)」だけではジャンプしないのかの意味）

お時間のあるとき、よろしくお願いします。

回答No.3

kagakusuki

>タイトル「QNo8987684_Excel_任意の行数、列数のデータを合体VBA」は現れるのですが、実行ボタンがグレーアウトして押せません。

　申し訳御座いません。「QNo8987684_Excel_任意の行数、列数のデータを合体VBA」は仮に付けた名前でして、いろいろ試してみました処、どうも「、」が付いた名前ですとその様な現象が現れるようです。(何故そうなるかという原因までは解りませんが)
　ですから、マクロの名称を何か別の「、」が付かない名前に変更して頂ければ正常に実行する事が出来る様になると思います。(因みに、「Microsoft Visual Basic for Applications」のウィンドウでメニューの[実行]ボタン→[Sub/ユーザーホームの実行]と操作するか、或いは[F5]キーを押す方法であれば、「、」付きの名称でも実行させる事が出来る様です)
　尚、「・」や「.」、「/」、「?」、「￥」、空白、等々、他にもマクロの名称として使用出来ない文字は幾つか御座いますが、そういった使用出来ない文字を使いますと、大抵の場合は「コンパイルエラー」の表示が現れて使えない文字である事をExcelが教えてくれるのですが、「、」の場合はその様な表示が現れなかったため気付きませんでした。

お礼

なんと、「、」が制限に入っているとは知りませんでした。
たしかにマクロウインドウの前に▲（再生）ボタンではエラーが出なく、あたまに数字はないし、困っていましたが、とったら実行できました。

補足

理解のために、お時間のあるとき解説いただければ幸いです。

1| With Sheets("Sheet1")
2|Sheets("Sheet1").Range("A1:AX" & .Range("A" & RowS.Count).End(xlUp).Row).Copy
3|End With

の部分ですが、「With｜」で始まり、「End With」で終わる（くくりが終わる）という見方で理解しているのですが、

Q1)この3行の真ん中の行（2行目）だけでは動作しない
（やりたいことが起こらない）理由が理解できません。

「With｜」で始まり、「End With」で終わる箇所が2箇所（2段落）ある理由を解説いただけたら幸いです。

また

Sheets("Sheet3").Range("A1").PasteSpecial Paste:=xlPasteValuesAndNumberFormats
のような記述で、

Q2)xlPasteValuesAndNumberFormats」とValueだけでなく、NumberFormatsと宣言してるのはなぜでしょうか。数字も値（Value）は数字そのものとして処理されると考えていましたので、理由が知りたいと思いました（データに日付は出現しません）

最後にこれは単なる関心なのですが、

Q3) PasteSpecial Paste:=xlPasteValues
のところで、イコールだけのPaste=xlPasteValues
ではなく、VBAが「:=」という記述の型を使っている理由をお知りでしたら教えてください。
（「:」は、セパレーターに見えるのですが）

回答No.2

kagakusuki

>・コピーして貼り付ける（複写する）のは、値だけ
>（罫線とか計算式は不要）

との事ですが、日付や時刻等のデータは含まれていないのでしょうか?
　もし日付や時刻のデータが含まれていた場合には、値だけコピー&貼り付けを行っただけでは単なるシリアル値の数値が表示されるだけで、日付や時刻は表示されませんので、値だけではなく、表示形式もコピー&貼り付けを行なわなければなりません。

Sub QNo8987684_Excel_任意の行数、列数のデータを合体VBA()

With Sheets("Sheet1")
Sheets("Sheet1").Range("A1:AX" & .Range("A" & RowS.Count).End(xlUp).Row).Copy
End With
Sheets("Sheet3").Range("A1").PasteSpecial Paste:=xlPasteValuesAndNumberFormats
With Sheets("Sheet2")
Sheets("Sheet2").Range("A2:AX" & .Range("A" & RowS.Count).End(xlUp).Row).Copy
End With
Sheets("Sheet3").Range("A" & RowS.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValuesAndNumberFormats

End Sub

お礼

お２人ともシンプルなコードをありがとうございます。
理解に難しい点がありますので、補足欄で１つ１つ伺いますが、お時間がありましたらおつきあいください。

補足

記述していただいたコードは、Excelの「開発」リボン中の→「マクロ」から開くマクロウインドウにタイトル「QNo8987684_Excel_任意の行数、列数のデータを合体VBA」は現れるのですが、実行ボタンがグレーアウトして押せません。

他のマクロは実行できます。

Excel2013、2007とも同じ現象でしたが、何が原因かおわかりになりますでしょうか。

回答No.1

keithin

転記したい具体的なセル範囲を取得（指定）して、どんどんと転記するだけです。

sub macro1()
　dim r1 as long
　dim r2 as long

　r1 = worksheets("Sheet1").range("A65536").end(xlup).row
　worksheets("Sheet3").range("A1:AX" & r1).value _
　= worksheets("Sheet1").range("A1:AX" & r1).value

　r2 = worksheets("Sheet2").range("A65536").end(xlup).row
　worksheets("Sheet3").range("A2:AX" & r2).offset(r1 - 1, 0).value _
　= worksheets("Sheet2").range("A2:AX" & r2).value
end sub

お礼

お２人ともシンプルなコードをありがとうございます。
理解に難しい点がありますので、補足欄で１つ１つ伺いますが、お時間がありましたらおつきあいください。

補足

いつも記述いただくコードはわかりやすく感謝しています。
基本的な質問となりますが、２つ質問があります。

＞r1 = worksheets("Sheet1").range("A65536").end(xlup).row

のworksheets("Sheet1")は、シートのSheet1であることはわかるのですが、
１）range("A65536")と単純に記述しているのは、A列を検索しているのでしょうか。
　その場合、なぜA列だけを対象にしているのでしょうか。
２）end(xlup).rowは、Ctrl+↑の動作と思われますが、この行では何をやっているのかがわかりません。

よろしくお願いします。