[[20161124170903]] 『マクロ オートフィルタでデータが1件も無かった氏x(pk) ページの最後に飛ぶ

[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]

 

『マクロ オートフィルタでデータが1件も無かった時』(pk)

いつも大変お世話になっております。

マクロでフィルタをかけて他のシートにコピーペーストしているのですが
データが0件の時にはコピーをしないという方法はありますでしょうか?
0件のデータをコピペするとエクセルが重くなってしまって困っております。

以下のマクロに0件の時はペーストしないという式は
どのように入れたらよろしいでしょうか?
(若しくは可能であれば、
0件の時にはA1:J1のみコピペして
A2にはシート名を、B2:J2は0と表示させたいです。)

Range("A1").Select

    Range(Selection, Selection.End(xlToRight)).Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$J$1").AutoFilter Field:=1, Criteria1:="=*SCA*" _
        , Operator:=xlAnd
    Range("A1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Sheets("SCA").Select
    Range("A1").Select
    ActiveSheet.Paste

説明がわかりにくくすみません、
ご教示宜しくお願い致します。

< 使用 Excel:Excel2010、使用 OS:Windows7 >


 元シートを ActiveSheet に固定しているところは少し気になりますが、一例です。
 (ちょこっと直しました 17:53)

 Sub Sample()
    Dim sh As Worksheet
    Set sh = Sheets("SCA")
    sh.Cells.ClearContents
    ActiveSheet.AutoFilterMode = False  '念のため
    Range("A1").AutoFilter Field:=1, Criteria1:="*SCA*"
    With ActiveSheet.AutoFilter.Range
        If .Columns(1).SpecialCells(xlCellTypeVisible).Count > 1 Then
            .Copy sh.Range("A1")
        Else
            .Rows(1).Copy sh.Range("A1")
            sh.Range("A1").CurrentRegion.Offset(1).Value = 0
            sh.Range("A2").Value = ActiveSheet.Name
        End If
    End With
 End Sub

(β) 2016/11/24(木) 17:36


ども^^

参考になれば。。。

Sub test()

    Dim rngFrom As Range
    Dim rngTo As Range

    Set rngFrom = ActiveSheet.Range("A1")
    Set rngTo = Sheets("SCA").Range("A1")

    rngTo.CurrentRegion.ClearContents

    With rngFrom.CurrentRegion
        .AutoFilter Field:=1, Criteria1:="=*SCA*"
        .Copy rngTo
        .AutoFilter
    End With

    With rngTo.CurrentRegion
        If .Rows.Count = 1 Then
            .Offset(1).Value = 0
            .Cells(2, 1).Value = rngFrom.Worksheet.Name
        End If
    End With
End Sub
(まっつわん) 2016/11/24(木) 18:16

 >>0件のデータをコピペするとエクセルが重くなってしまって困っております。 

 ところで、【重い】というのは、できあがったブックサイズが大きくなるということではなく
 処理時間が長くなる ということですよね?

 その原因は

    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy

 です。1行目のタイトルしかない状態の時、A1 の End(xldown) は 1048576 になりますから
 1048576行のコピーになるからです。

 必要な領域のみコピーすれば、軽いです。
 私のコードではちょっと、抽出ゼロ件 という言葉に引きずられてしまいましたが(と、いいわけ)
 CurrentRegionなりAutoFilter.Range なり、いずれでもいいですけど、そこをコピーする。
 で、まっつわんさんの方法のように、コピー結果が1行なら という処理のほうがシンプルでしたね。
(β) 2016/11/24(木) 20:11

(β) 様
(まっつわん)様

早速ご教示頂きありがとうございます!!!

教えて頂いたマクロをお二人の試してみましたが無事に出来ました!

なのですが私の伝え方が悪くてすみません(><)
0件の場合、A2にシート名を記載するところなのですが
元のシート名ではなくて、貼り付ける先のシート名を記載したいのです(><)

なので、これでいうと SCA と記載したいのです。
すみません、こちらを貼り付ける先のシート名を記載する場合には
どのようにしたらよろしいでしょうか?

度々すみません。
ご教示宜しくお願い致しますm(__)m

(β) 様から教えて頂いた原因についてはこれから解読させて頂きます!
ご丁寧に教えて頂き本当にありがとうございます。
(pk) 2016/11/25(金) 09:55


>これでいうと SCA と記載したいのです。
>すみません、こちらを貼り付ける先のシート名を記載する場合には
>どのようにしたらよろしいでしょうか?

rngFrom.Worksheet.Name

rngTo.Worksheet.Name
に変えてください。

読んでみて、何となくシートの名前って解りませんかねぇ・・・

(まっつわん) 2016/11/25(金) 12:42


 私のコードであれば

 sh.Range("A2").Value = ActiveSheet.Name

 sh.Range("A2").Value = sh.Name

 でも????
 貼り付け先シート名って、シートタブを見ればわかりますよね?
 印刷なんかの場合を想定?
 であれば ページ設定で フッターあたりに シート名を表示させておく手もありますよね。

 どのシートにデータがなかったのか、その情報のほうが有益だと思ったりしますが?

(β) 2016/11/25(金) 13:14


(まっつわん)様
ご教示ありがとうございました!!!

そうですね、よく見たらtoとfromの違いなんですね^^;;;
勉強になりました!
コードが全然わかっておらずすみません(><)

ご丁寧に教えて頂き本当に助かりました。
この度はありがとうございました。
また宜しくお願い致しますm(__)m

************************

(β)様
ご教示ありがとうございました!!!

シートタブ???
いえ、印刷の想定はしていません(><)
自分ではマクロの記録機能を使ってやっているので
間違えているところがたくさんあるのかもしれません。。。

ご丁寧に教えて頂き本当にありがとうございました。
大変助かりました。
また今後とも宜しくお願い致しますm(__)m

(pk) 2016/11/25(金) 14:05


コメント返信:

[ 一覧(最新更新順) ]


YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki. Modified by kazu.