vba の Selection.PasteSpecial で非表示行の扱い

Selection.PasteSpecial で非表示行を飛ばして実行する方法はあるでしょうか。

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

• コピー、ペースト付近のコードです。
  i2 = Range("Y2").End(xlDown).Row
  Range("Y3:Y" & i2).Select
  Selection.Copy
  Range("B3").Select
  Selection.PasteSpecial
  貼り付け時に非表示行にデータが入らないようにしたいです。
  よろしくお願いいたします。

    補足日時：2020/03/07 07:59

• Qchan1962様のNo.9、No.10の通り試した結果正常に動作しました。
  私が使用するファイルはA列からO列にフィルタがかけられており、C列の空白欄を非表示にしてあります。
  同じ条件にして試したところ、図の結果になりました。

  
    補足日時：2020/03/08 12:01

No.8 ベストアンサー 回答者： Qchan1962 回答日時： 2020/03/07 13:01

＃４です。

非表示行のあるシートをコピペしたいのだと解釈しましたが、　
回答としてSelection.PasteSpecialで非表示行を飛ばして処理するのは、とびセルになり、コピー元と範囲が違うため無理かと思います。
やはり、Selection.PasteSpecialでなく参照式のが良いと思うのですが。
代替え案　ループ処理による参照式
下記では、ダメですか？
Sub Sample()
Dim i2 As Long, i As Long, j As Long
　　i = 3: j = 3
　　i2 = Range("Y2").End(xlDown).Row
　　Do While j < i2 + 1
　　　　If Rows(i).Hidden = False Then
　　　　　　Cells(i, "B") = Cells(j, "Y")
　　　　　　j = j + 1
　　　　End If
　　　　i = i + 1
　　Loop
End Sub

値だけでない場合は、Cells(i, "B") = Cells(j, "Y")参照式でなくコピーなどに、、
工夫してください。

この回答へのお礼

この回答を充分に検証しないまま、後の物を試してしまいました。
このコードで完璧に動作するようになりました。
ありがとうございました。

お礼日時：2020/03/08 15:26

No.11 回答者： Qchan1962 回答日時： 2020/03/08 13:12

お手数をおかけいたしました。

フィルタで非表示ですか。。そうですね、フィルタを当然想定できますね。。
古い記憶で忘れていましたが、であれば、 Application.Transposeで一気に書き出すのは、多分使えないですね。

＃８の方法で1つずつ出力する方法で処理してください。

No.10 回答者： Qchan1962 回答日時： 2020/03/07 23:15

#9の返信に

気になる点は、
シート情報がないので明示していない。
i2 = Range("Y2").End(xlDown).Row
これは、補足にあるコードですが、Y2に値が入っていないと希望の値を得られません。

検証して不具合を発見しました。
返信にある現象ではありませんが、
配列再構成時、非表示セルの値を抽出する変数値がずれていますね。すみません。
正　Pst(i) = Cells(i+3, 2)

もし、違うのであれば、具体的に示してくださいね。（画像を付けるなどして）
＞複数回別セルに代入されてしまうようです。
書き込みは1度しか行っていませんね？

No.9 回答者： Qchan1962 回答日時： 2020/03/07 13:43

＃８　追加

食事しながら、、処理速度など気になったので
値を前提で少し手を加えました。100行くらいなら変わらないと思いますが、
5000とかだと変わるかな。

Sub Sample1()
Dim i2 As Long, i As Long, j As Long
Dim cpy As Variant, Pst() As Variant
j = 1
i2 = Range("Y2").End(xlDown).Row
cpy = Range("Y3:Y" & i2)
Do While j < i2 - 1
ReDim Preserve Pst(i)
If Rows(i + 3).Hidden = False Then
Pst(i) = cpy(j, 1)
j = j + 1
Else
Pst(i) = Cells(i, 2)
End If
i = i + 1
Loop
Range("B3").Resize(UBound(Pst) + 1).Value = Application.Transpose(Pst)
End Sub
取得　cpy = Range("Y3:Y" & i2)　と
書き出し　Range("B3").Resize(UBound(Pst) + 1).Value = Application.Transpose(Pst)　を
纏めました。

この回答へのお礼

回答ありがとうございます。
非表示セルは飛ばして代入されましたがY列の一番上と2番目のデータが複数回別セルに代入されてしまうようです。

お礼日時：2020/03/07 17:35

No.7 回答者： めぐみん_ 回答日時： 2020/03/07 11:23

＞貼り付け時に非表示行にデータが入らないようにしたいです。

ってNo.2のお礼の

＞Copy 側は非表示はなく100行ほどの連続データです。

と矛盾してない？
Sheetを変えて又は行をもっとずらして貼り付けてるならともかく、Ｙ列の行は全て表示されているけどＢ列の行は非表示がある・・・
そんな方法ってあるの？
単なる私の経験不足なのかな？（十数年たっても初級レベルだし）

方法的にはコピペより代入していくのが良いように思いますが、上の件が謎ですねん。

この回答へのお礼

すみません、矛盾しています。
「コピ一するデータは非表示行を含んだ連続デ一タです」が正しい表現でした。

お礼日時：2020/03/07 13:04

No.6 回答者： tom04 回答日時： 2020/03/07 10:32

No.1・3です。

Y列の指定範囲内の非表示でない行をB3セルにコピー＆ペーストしたい！というコトですね。

Selectせずに

＞Range("Y3:Y" & i2).Select
＞Selection.Copy
＞Selection.PasteSpecial

を
＞Range("Y3:Y" & i2).SpecialCells(xlCellTypeVisible).Copy Range("B3")

（書式も含めてすべてがコピー＆ペーストされます）

のようにしてはどうでしょうか？m(_ _)m

この回答へのお礼

回答ありがとうございます。
コピー時ではなく、B列にペースト時に非表示セルを飛ばしてほしいです。

お礼日時：2020/03/07 11:10

No.5 回答者： めぐみん_ 回答日時： 2020/03/06 15:30

既に同じ憶測は出ていますが、最初に質問文から『貼り付ける際に非表示行を避けてクリップボードにある情報を貼り付けていきたい』と言うのは読み取れたのですが、

Copyが任意の作業で行われるのか否かもありますので可能なら前後のコード提示も必要なのかもしれませんね。

No.4 回答者： Qchan1962 回答日時： 2020/03/06 15:23

皆さんの回答にあるようにどのように非表示にしているかによると思いますが

Selection.、、なのでSelectする段階で条件設定をする必要があるかと
ループで行っているのか、フィルター機能で行っているのか、または他の方法で纏めてSelectしているのか、分かりませんが、
Hidden プロパティで判定して飛ばすことは出来そうですが、、
非表示行を飛ばして実行する場合、Selectionは、ふさわしくないかも知れません。

Dim i As Long
For i = 1 To 100
If Rows(i).Hidden = False Then
~~.PasteSpecial
End If
Next

No.3 回答者： tom04 回答日時： 2020/03/06 14:59

No.1です。

＞応答なしになってしまいました。

前回の回答にも記載したのですが、
非表示でないセルに対してどんな操作をやるのか？によってコードは変わってきます。

それが判らなかったので単純にループさせるコードにしましたが、
極端に広い範囲（行全体とか、列全体など）を指定すると
「応答なし」になります。

すなわち何をしたいのか？が判ればもっと具体的なアドバイスができると思います。m(_ _)m

No.2 回答者： めぐみん_ 回答日時： 2020/03/06 14:40

Copy 側がどのような条件でセル範囲を与えられているのかによるのではないかな？

連続なのか今回のように可視セルのみ選択しているのか。

この回答へのお礼

回答ありがとうございました。
Copy 側は非表示はなく100行ほどの連続データです。

お礼日時：2020/03/06 14:52