Excel (VBA)

Excel VBAに関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(Windows 8.1 : Excel 2010)
フィルタで絞り込んだ状態の表を他のシートにコピーしたい
投稿日時: 21/03/09 00:37:51
投稿者: かうきのおやじ

購入日・顧客名・顧客番号・品名・個数の履歴を記録した表があります。
そこから複数の特定の条件(購入日や顧客番号)に
フィルタで絞り込んだ状態の表のデータ部分だけを加工用の別シートに転記したいのですが、
見出し行の次の行番号は絞り込む条件ごとに当然変化します。
相対参照で記録した場合、A4セルの見出しから↓で下に降ろしたその時の座標が
 
 Range("A4").Select 
 ActiveCell.Offset(5505, 0).Range("A1").Select
 
のように記述されてしまうので他の条件の時には使えなくなってしまいます。
 
マクロに関しては、参考書を見ながらどうにか作れるレベルですので
最下行を選択する方法は分かるので見出し行を含めてコピーするのはなんとかできるのですが、
データ部分だけをできるだけ簡素に範囲指定する方法をご指南下さい。

回答
投稿日時: 21/03/09 08:18:32
投稿者: simple

考え方は次のとおりです。
・オートフィルタを掛ける前に、データ部分だけを対象としたRange変数を作っておきます。
 (これは、CurrentRegionやEndプロパティを使う方法が採れますね。)
・フィルタを掛けたあとで、その部分をコピーすればよいです。
・オートフィルタが掛かっているときは、可視セルという限定をしなくても、
  自動的に可視セルに限定されたものがコピーペイストされる仕様です。
 
また、オートフィルタが掛かっている状態であれば、以下のような手法も採れます。

Sub test()
    Dim myRange As Range
    Dim body As Range
    
    Set myRange = Worksheets("Sheet1").AutoFilter.Range     '(1)
    Set body = Intersect(myRange, myRange.Offset(1))        '(2)
    body.Copy Worksheets("Sheet2").Range("A1")              '(3)
End Sub
【補足】
(1)AutofilterオブジェクトのRangeプロパティで、対象とするセル範囲が取得できます。
(2)それと、それを一つ下にずらしたものとの共通部分をとれば、それがデータ部分です。
(3)可視セルだけがコピーされます。

投稿日時: 21/03/11 00:09:16
投稿者: かうきのおやじ

simpleさん、ありがとうございます。
 
フィルタをかけるところまでを含めてマクロで動作させようと思うと前半のご説明のように
CurrentRegionやEndプロパティを使う方法を採るって事で理解すればいいのですね。
「Range変数を作っておく」に関しては頑張って勉強したいと思います。
 
他の方法があるかもしれませんのでこの質問スレはまだ閉じないでおきます。

回答
投稿日時: 21/03/11 06:30:42
投稿者: simple

1)
例文はこんな感じです。

Sub test()
    Dim lastRow     As Long
    Dim lastColumn  As Long
    Dim target      As Range
    Dim body        As Range
    
    lastRow = Cells(Rows.Count, "A").End(xlUp).Row
    lastColumn = Cells(1, Columns.Count).End(xlToLeft).Column
    
    Set target = Range(Cells(1, 1), Cells(lastRow, lastColumn)) '見出しを含む全体
    Set body = Range(Cells(2, 1), Cells(lastRow, lastColumn))   'データ部分
    
    target.AutoFilter Field:=1, Criteria1:="=5", Operator:=xlOr, Criteria2:="=6"
    body.Copy Worksheets("Sheet2").Range("A1")  '抽出されたデータ部分だけをコピーする
End Sub
(単純化のために、アクティブシートを対象とした書き方にしていますが、
  本来は、セル範囲にはシートを指定する書き方が望ましいと思います。)
 
(2)【補足】
上記は、セルの指定方法で良く出てくる一番基本的なものです。
「セル範囲の指定」の仕方は、Excelでマクロを書くとき、
常に出てくるものですので、この際、よく学習されることをお薦めします。
 
参考資料として、以下を上げておきます。
「セル範囲の指定方法:Excel VBA入門」
http://www.eurus.dti.ne.jp/~yoneyama/Excel/vba/vba_cell.html
 
(なお、このなかの例文では、図解する関係で、
  逐一、選択(Select)する例文になっていますが、ここは割り引いて見てください。
  つまり、セルを指定する部分に焦点を当てて読んで下さい。
  例えば、
  Range("A1").End(xlDown).Select
  というコードがありますが、常に Selectするんだなと読まずに、
  Range("A1").End(xlDown)に重点を置いて見て下さい。
  普通は、
  最終行 = Range("A1").End(xlDown).Row
  などという使い方をすることが多く、セルの選択は避けるのが一般的です。)
 
(3)
なお、AutoFileterについては、通常は、
↓のようにセル範囲の一部を指定すれば、あとは忖度してくれることが多い。(内容はサンプルです)
    ActiveSheet.Range("A1").AutoFilter Field:=1, Criteria1:="=5", Operator:=xlOr, Criteria2:="=6"
こういう感じのコードでフィルタを掛けて、私が前の発言であげたコードを繋げるのが、
実際的な使い方かなと思います。
 
(4)【余計なお世話でしょうけど】
>他の方法があるかもしれませんのでこの質問スレはまだ閉じないでおきます。
それで結構ですよ。
ただし、満塁逆転ホームラン的なものに期待しないことです。
私は必要なことはほぼ書いたつもりです。
セル範囲の指定の仕方などの基本をきちんと学習されることをお薦めします。
# まあ、有り体に言えば、「もっと丁寧に説明してくださいよ。誰か他の方、頼みますよ」と
# いう意味なんでしょうから、上記のとおり補足を致しました。参考にしてください。

回答
投稿日時: 21/03/12 18:05:53
投稿者: simple

ご返事が無いようで残念です。
つらつらとメモしてみます。
 
(1)
まず、あなたが当惑していることはこんなことですよね。
 
例として、こんなデータだったとします。

    A列    B列
1   商品   金額
2   A      100
3   B      120
4   C      140
5   A      160 
6   C      180

こうしたデータがあり、商品="C"でフィルタを掛けたうえで、
抽出したデータだけを別シートに転記したいと。
 
"C"だけだから、4行目で、次は6行目だと、
こうした不規則なものをどうやったら簡単に転記対象にできるのか?
ということがあなたの困惑したことだろうと想像します。
 
私の考え方のキモは、以下のようなことです。
・ズバリ言えば、4行目とか、6行目とかの情報は必要ないんです。
  以下、それを説明します。
・フィルタを掛ける前のデータ部分(つまりA2:B6)のセル範囲をそのままコピーすれば、
  実際は抽出されたものだけがコピーペイストされる
のです。
  これは不思議と言えば不思議なんだが、100%確実にそうなります。
  そうした"仕様"を理解することがまず必要です。
  そして、そういう仕様であるなら、それを利用しない手はないわけです。
 
・A2:B6という元々のデータ範囲を特定することは、さほど難しくないはずです。
・フィルタを掛ける前に、A列の最終行(6行目)を求めておけば、
  2行目から始まることは固定だから、A2からB6までと簡単に求まりますよね。
・これを Set 変数 = Range("A2:B6")のように変数に持って置いて、
  フィルタを掛けてから、それをコピーペイストすればよいわけです。
 
(2)
投稿日時: 21/03/09 08:18:32の後半で書いたことは説明を付けているので、
それを読んでいただけば良いのですが、
ポイントは、 以下です。
    ・AutofilterのRangeプロパティというオートフィルタの対象範囲を返してくれる
      便利なものがあるということ。
      これがあれば、End(xlUP)だとかなんとかで、最終行を求めたりということは不要に
      なるわけです。
    ・IntersectとOffset(1)の組みあわせも、結構、気の利いた感じがしますよね。

回答
投稿日時: 21/03/13 09:51:36
投稿者: MMYS

データ部分だけをできるだけ簡素に範囲指定する方法をご指南下さい。[/quote]
 
CurrentRegionプロパティ を使いましょう。
http://officetanaka.net/excel/vba/tips/tips155c.htm
 

投稿日時: 21/03/13 22:33:30
投稿者: かうきのおやじ

simpleさん、返事が遅くなってすみませんでした。
フィルタを掛ける前のデータ範囲全体を指定してからフィルタを掛けても
ちゃんと絞り込んだ範囲(可視セル)だけを認識してコピーできるということなんですね。
それで納得できました。
 
ちょっと本題からは逸れますが、
IF関数の戻り値がゼロになる時に「0」を表示させたくないから「""」として、
そのシートを資料として提出するときに、表全体をコピーしてそのまま値のみ貼り付けして送ったら
他のワークシートに貼り付けた時にエラーになると指摘されたんです。
そのセルには何も入っていなくて表示形式も「標準」となっていて空っぽのはずなのに何かがじゃまをしてるんですね。試しにDeleteを押してみたらエラーが消えたんです。
 
エクセルってまだまだ知らない事がいっぱいあるってことを痛感してます。
 
ありがとうございました。