VBA Selectの省略

質問

こんばんは、VBAのSelectメソッドの省略について質問させてください。
VBAにおいてSelectを使用すると処理が遅くなると聞いたことがあるので、なるべくSelectを避けるべく以下のVBAを記述しました。
内容は、シート「りんご」のA列～AN列に入っているデータを見出し項目を含めてコピー、そしてSheet1にペーストするというものです。（行数は毎回変化します。）
ところが、以下を実行したところ、コピー＆ペーストされたのは１行目の見出し項目のみでした。一方、「Range("A1:AN" ～」の前に「Sheets("りんご").Select」を入れたところ、全てのデータをコピー＆ペーストすることができました。
この場合は、Selectを使用せずデータをコピー＆ペーストすることは不可能なのでしょうか…？！どなたかご回答いただけると嬉しいです、よろしくお願いいたします。

　'対象データのコピー＆ペースト
Sheets("りんご").Range("A1:AN" & Cells(1).CurrentRegion.Rows.Count).Copy
Sheets("Sheet1").Paste

回答No.5

real beatin（@realbeatin） · ベストアンサー

No.1, 3です。こちらから追加補足します。

> VBAにおいてSelectを使用すると処理が遅くなると聞いたことがあるので、
> なるべくSelectを避けるべく以下のVBAを記述しました。
(改行は筆者)

Selectメソッド 自体は、
必要な（或いは避けられない）場面もあるし、
Selectした方が処理が速くなる場面もありますし、
より高速な処理を目指した時に
わざとSelectを用いて優れた結果に繋がるようなケースもあります。
問題になるのは、
Selectメソッドと、それに続くSelectionオブジェクト、
この、組合わせ、のことであって、
"避けるべく"理由というのも、
「処理が遅くなる」からだけではありません。
スクリプトの行数（文字数）が増える→読みづらい→後から編集するのが面倒
というのが、Select+Selectionが忌避される理由としては普通なのでは、と思います。
特にSelectionについては、
多様なオブジェクトを格納する為の（Global.Selection）プロパティですので、
会話の組み立てが苦手な人の指示代名詞（これそれ...）の如く、
言語としての不確実性を残す傾向にある、とも言えます。
簡素な記述にしておくことは、コーディングに際して、
物理的にも、思考過程の面から言っても、早く仕事を片付けられる、
というメリットにも繋がることだと考えます。

コピーの仕方、にも色々あって、それぞれ一長一短、と私は思いますが、
一応、補足しておきます。
「セル範囲をコピーして、シートに貼り付ける」
というのが、質問での処理の流れです。
　　With Sheets("りんご")
　　　　.Range("A1:AN" & .Cells(1).CurrentRegion.Rows.Count).Copy
　　End With
　　Sheets("Sheet1").Paste
でも実はこれ、Sheet1で選択されたセル、またはセル以外のオブジェクト
に貼り付ける、という処理なので、
ある意味、Selectionに依存していることになります。
そこで、貼付け先を明示的。限定的にして、
「セル範囲をコピーして、指定のセル範囲に貼り付ける」
　　With Sheets("りんご")
　　　　.Range("A1:AN" & .Cells(1).CurrentRegion.Rows.Count).Copy
　　End With
　　Sheets("Sheet1").Cells(1).PasteSpecial Paste:=xlPasteAll
のように書くことも出来ます。
.PasteSpecialメソッドは、値の貼付けを初め、Excelの[形式を選択して貼付け]
に対応した複数の貼付け方法を提供するものです。
最初の回答の時点で、こちらを提示するか結構迷ったのですが、
訊かれていること以外に踏み込んで本題が霞むのは本意でないので、
No.1のように書くことにしました。
普通は、これらの記述に続けて、
　　Application.CutCopyMode = 0
として、貼付け待機状態（コピー元周囲の点滅アニメ―ション）を解除したり、
条件付きでクリップボードを解放したり、という処理に繋げるものでもあります。
また、フツーにコピペするだけでしたら、No.4でのご回答のように、
　　With Sheets("りんご")
　　　　.Range("A1:AN" & .Cells(1).CurrentRegion.Rows.Count).Copy Destination:=Sheets("Sheet1").Cells(1)
　　End With
[コピー先のセル範囲]を引数に指定して、直接貼付け、
クリップボードを経由しないで、より簡潔に書ける方法もあります。

先に一長一短、と書きました。
誰にでも理解され易い、
初学者にも理解され易い、
というのも、それぞれ、ひとつの尺度だと常々思っています。
（逆に、"処理が遅くなる"というのも、ひとつの尺度に過ぎません。）
（また、"処理が遅くなる"のを避けることが、ご質問の主題とは捉えていません。）
私としては、段階を追って、実践する中で、
今やろうとしていることを確かなものにするように
ひとつひとつ覚えていって貰えたらな、と思っています。
そういう意味ではこの回答自体まったくの蛇足なのですけれども、
ご自分のペースで、急がなくてもいいですよ、ってな感じの補足でした。

お礼

Destinationの使用法についてご教示いただいてありがとうございます！確かに、誰が見ても分かりやすい記述方法というのも大事なので、あまりに処理が速いことにこだわってもだめですね( ;∀;)何度もご回答いただきありがとうございます、ベストアンサーに選ばせていただきますね！

回答No.6

SI299792

CurrentRegion は空白のセルで囲まれた範囲を読み取るもので、最終行の指定はできません。途中に空白があればそこまでになります。
最終行までコピーしたいなら、はSpecialCellsを使います
　　Dim L As Long
'
　　L = [A1].SpecialCells(xlLastCell).Row
　　Sheets("りんご").Range("A1:AN" & L).Copy
　　Sheets("Sheet1").Paste

次の書き方もあります。
　　Dim L As Long
'
　　L = [A1].SpecialCells(xlLastCell).Row
　　Sheets("りんご").Range("A1:AN" & L).Copy Sheets("Sheet1").[A1]

数字だけコピーする場合、次の方法がいいです。
　　Dim L As Long
'
　　L = [A1].SpecialCells(xlLastCell).Row
　　Sheets("Sheet1").Range("A1:AN" & L) = Sheets("りんご").Range("A1:AN" & L).Value

参考URL：

http://www.niji.or.jp/home/toru/notes/8.html

お礼

確かに何度かテストで試したところ、空白セルがあるとそれ以後のデータは選択しなくなりました！(；゜Д゜)分かりやすいページもご提供いただき、ありがとうございます！参考にさせていただきますね！

回答No.4

nan93850673

Cells(1)と書くと、ActiveSheet.Cells(1)になります。
Sheets("りんご").Selectを入れると、ActiveSheetが"リンゴ"になるので想定した通りの動作になったのでしょう。

>VBAにおいてSelectを使用すると処理が遅くなると

Selectというか、ActiveSheetを変更する処理は遅いですが、それ以上に

>Sheets("りんご").Range("A1:AN" & Cells(1).CurrentRegion.Rows.Count).Copy
>Sheets("Sheet1").Paste

クリップボードを使うCopyメソッドは処理に時間が掛かります。
Destinationを指定した、クリップボードを介さないCopyメソッドへ変更しましょう。

お礼

クリップボードを使うのにも処理に時間がかかるのですね！ご指摘ありがとうございます<m(__)m>早速、Destinationを使用する方法を調べてみようと思います。

回答No.3

real beatin（@realbeatin）

んー、と。
本題と逸れた方向に向かう流れ、は停めたいのですが、、、。

NO.1で私が書いた、
> Sheets("りんご")のようなオブジェクトアクセスを繰り返すのも
> 記述を煩雑にし処理を遅くする原因になりますから、
については、一般的な内容だとは考えていますが、
　記述を煩雑にし処理を遅くする原因に[も]なりますから、
ぐらいに書くべきだったかな、と。
確かに１、２回の繰り返しでは、処理速度に変化が見られたとしても、
測定誤差レベルの微かなものでしかありませんから、あまり意味ないです。
でもこれらの記述をループ処理の中で繰り返し実行するかも知れない、
今後、外部オブジェクト等アクセスに時間の掛かるオブジェクトを
扱う機会があるかも知れない、
という、プログラムの基本的なことを、
応用場面で困ることのないように覚えておいて貰いたいことを記した
つもりです。

以下、蛇足になります。
　Cells(1)
なのか
　Range("A1")
なのか
という、このページの主題から逸れた議論（？）
が投げられているようですが、
　単セルの扱いはCellsが有利、
　複数セルで、参照文字列による参照が可能な場合はRangeが有利、
（掻い摘んで、あえて曖昧に書きました）
といった、指標ともいうべき複数の記事が出始めてから、十数年経って、
今、議論に値する内容ではない、と私は思います。
提議された回答者さん、
imogasiさんがリンク張ることの多い超有名ライターさんも、
何度か書かれていらっしゃいますし、、、。
多数のライターさんが同趣旨のことを書いていらっしゃいますし、
このことを知らなくても
実戦的なコーディング経験を積んでいるうちに、多くのVBA編集者が、
こういった使い分けに収束しているのではないかと私は思います。
または私なりの理由づけを説明することも出来ますが、
限られた文字数では到底無理なことです。
つまり議論にはなりようのないことなのですが、
このサイトの運営趣旨に倣って閲覧者のご判断に委ねます。
私としては、そこは本題とは関係ないですから、
あんまり引っかからないでね、と質問者さんへのメッセージ
のつもりで書いています。

　20170920　　realbeatin

お礼

単一セルはCells,複数セルはRangeといった使い分けは全然考えていなかったです…！目から鱗です(^O^)ＶＢＡは奥が深いですね…、道のりはまだまだ長そうです、ご回答いただき、ありがとうございました！

回答No.2

imogasi

Cells(1). はSheets("りんご")の第1番目（Indexが１）、すなわちAN１を指してしまって、Range("A1:AN１")すなわち、第1行目を選択してコピーしてしまう。
だから、この質問は、.ＳＥＬＥＣＴの問題でないのでは。
CurrentRegion.Rows.Countが１になってしまう。
CurrentRegion.は、単一セルを指定して、そのセルを含むセル範囲を求めたいときに使う例をよく見る。複数セルの塊（連続セル）の範囲を指定して、CurrentRegionは使われるのを見たことがない（ただし私のVBAの経験は大したことないが）。
＞Cells(1).CurrentRegion.Rows.Countは、何をしようとしているのだろう。
難しく表現しているだけでは？
Sheets("りんご").Range("A1").CurrentRegion.Copyではだめなのかな。
Cells(1）は何をしようとしているのだろう？
また本件と直接関係ないと思うが、Cellsの直前に具体的にシートの修飾（限定）を加えるようにした方がよいように思う。
Sub test01()
ActiveCell.CurrentRegion.Select
End Sub
は
A1:C５にデータがある場合、
A1セルだけを選択していても、A2:C２を選択していても、実行後はA1:C5を範囲指定する。
ということは、解説に
式.CurrentRegion
式 Range オブジェクトを表す変数。
とあるから、複数セル範囲を指定しても、間違いではないとは思うが。
＞CurrentRegion.Rows.Count)
下記も上の私のデータ例では、４になり、A1:C4を範囲指定する。
Sub test()
Cells(1).CurrentRegion.Select
MsgBox Selection.Rows.Count
End Sub
それで
Sub test03()
Range("A1").CurrentRegion.Select
End Sub
と同じなので、質問のコードの書き方は、ややこしいだけでは？
＞処理が遅くなると聞いたことがあるので
マクロの記録の結果によく出るように、Selectを多用すると、私はコードが冗長になり、読むとき、うるさいので避けたい。
私のレベル（少ないコード数で繰り返しも大したことはない）は、処理の時間を気にするレベルではないので、そちらの観点は重く考えていない。
VBAで勉強することが他にたくさんある。

お礼

確かにRange("A1").CurrentRegionでもいけるような気がしてきました…(>_<)いったい何が違うんでしょう…。最終行やデータ選択のＶＢＡ沢山あるので、まだ使い分けがはっきり理解できないです(T_T)ご回答いただいて、ありがとうございました！

補足

おおお！自分でテストしてみてわかりました！どうも、Range("A1:AN" & Cells(1).CurrentRegion.Rows.Count).Copyのほうは、ANの列のデータまでしか選択してくれません。Range("A1").CurrentRegionはAN列以降の列のデータも選択してくれました！

回答No.1

real beatin（@realbeatin）

こんにちは。

Cells(1).CurrentRegion.Rows.Count
の部分にご注目。
ご提示の記述では、アクティブシートの[A1]セルを基準に
行数を取得しています。
意図しているのは、Sheets("りんご")の[A1]セルを基準に
行数を取得するということなのでは？
その場合、
　　Sheets("りんご").Range("A1:AN" & Sheets("りんご").Cells(1).CurrentRegion.Rows.Count).Copy
　　Sheets("Sheet1").Paste
のようにシートを適切に指定することになります。
Sheets("りんご")のようなオブジェクトアクセスを繰り返すのも
記述を煩雑にし処理を遅くする原因になりますから、
例えばWithステートメントを使って、記述の重複を避け、
　　With Sheets("りんご")
　　　　.Range("A1:AN" & .Cells(1).CurrentRegion.Rows.Count).Copy
　　End With
　　Sheets("Sheet1").Paste
のような書き方を選ぶのが一般的です。
　.Range　.Cells　のように先頭に . を付けて、
Sheets("りんご")を親オブジェクトに指定していることになります。

以上です。

お礼

なるほど！Cells(1)の前にシート名が隠されていたのですね…！どうも最終行までを指定するＶＢＡの理解がなくて…ありがとうございます！また一つ勉強になりました<m(__)m>