Excel VBA質問箱 IV

当質問箱は、有志のボランティア精神のおかげで成り立っています。
問題が解決したら、必ずお礼をしましょうね。
本サイトの基本方針をまとめました。こちら をご一読ください。

投稿種別の選択が必要です。ご注意ください。
迷惑投稿防止のため、URLの入力を制限しています。ご了承ください。


893 / 13644 ツリー ←次へ | 前へ→

【77676】VBAを使い、別シートにデータを抽出したい たけちゃんまん 15/11/27(金) 23:22 質問[未読]
【77677】Re:VBAを使い、別シートにデータを抽出した... β 15/11/27(金) 23:49 発言[未読]
【77679】Re:VBAを使い、別シートにデータを抽出した... たけちゃんまん 15/11/28(土) 0:32 お礼[未読]
【77681】Re:VBAを使い、別シートにデータを抽出した... β 15/11/28(土) 13:48 発言[未読]
【77685】Re:VBAを使い、別シートにデータを抽出した... たけちゃんまん 15/11/29(日) 11:54 お礼[未読]
【77695】Re:VBAを使い、別シートにデータを抽出した... β 15/11/30(月) 17:57 発言[未読]
【77678】Re:VBAを使い、別シートにデータを抽出した... β 15/11/28(土) 0:06 発言[未読]
【77680】Re:VBAを使い、別シートにデータを抽出した... たけちゃんまん 15/11/28(土) 0:48 お礼[未読]

【77676】VBAを使い、別シートにデータを抽出したい
質問  たけちゃんまん  - 15/11/27(金) 23:22 -

引用なし
パスワード
   はじめて投稿させて頂きます。
VBAの知識はほぼ無いに等しいのですが、本やサイトで検索し、自分なりに考え、昨日から色々と試していますが、エラーとなるか、正しく反映されません。

<目的>
5000行程でA〜AB列まで入力されているのデータベースがあります。データは日々、20行程追加します。
そのデータベースの商品コードと一致するデータを行ごと別シートに抽出したいです。

<データベースの列構成>
A/B/C/D/…/I/J/K/L/M/…
処理状況/メモ/番号/日付/…/商品コード/商品名/規格/数量/単位/…
済/(空白)/0001/11月1日/…/a001/みかん/大/100/個/…
(空白)/(空白)/0002/11月1日/…/b001/りんご/小/20/個/…
(空白)/(空白)/0003/11月1日/…/c001/もも/大/10/個/…
済/(空白)/0004/11月2日/…/b002/りんご/大/15/個/…
(空白)/(空白)/0005/11月2日/…/b001/りんご/小/20/個/…
(空白)/(空白)/0006/11月3日/…/b003/りんご/中/50/個/…
済/(空白)/0007/11月4日/…/a001/みかん/大/80/個/…
済/(空白)/0008/11月4日/…/a002/みかん/中/30/個/…
この様なデータを日々追加します。
※2行目がタイトルで、3行目以降が上記データとなります。

このデータの中から、I列の商品コードがb001とb002とb003の行だけを別シートに表示したいです。

ご教授頂けます様、宜しくお願い致します。




【77677】Re:VBAを使い、別シートにデータを抽出し...
発言  β  - 15/11/27(金) 23:49 -

引用なし
パスワード
   ▼たけちゃんまん さん

フィルターオプションやオートフィルター処理が適していると思います。
以下はフィルターオプション。
元シート名や転記先シート名は実際のものに変更してください。

Sub Sample()  'フィルターオプション
  Dim cols As Long
  Dim r As Range
  Dim shT As Worksheet
  
  Application.ScreenUpdating = False
  
  Set shT = Sheets("Sheet2") '転記シート
  shT.UsedRange.ClearContents
  
  With Sheets("Sheet1")    '元シート
    cols = .UsedRange.Columns.Count
    Set r = .Range("A1", .UsedRange).Offset(1)
    .Cells(1, cols + 2).Value = .Range("I2").Value '抽出項目タイトル
    .Cells(2, cols + 2).Resize(3).Value = WorksheetFunction.Transpose(Array("'=b001", "'=b002", "'=b003"))
    r.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=.Cells(1, cols + 2).CurrentRegion, _
                CopyToRange:=shT.Range("A1"), Unique:=False
    .Cells(1, cols + 2).CurrentRegion.Clear
  End With
  
  shT.Select
  
End Sub

【77678】Re:VBAを使い、別シートにデータを抽出し...
発言  β  - 15/11/28(土) 0:06 -

引用なし
パスワード
   ▼たけちゃんまん さん

オートフィルター版も。
ただし、xl2007以降限定。

Sub Sample2()  'オートフィルター
  Dim cols As Long
  Dim r As Range
  Dim shT As Worksheet
 
  Application.ScreenUpdating = False
 
  Set shT = Sheets("Sheet2") '転記シート
  shT.UsedRange.ClearContents
 
  With Sheets("Sheet1")    '元シート
    cols = .UsedRange.Columns.Count
    Set r = .Range("A1", .UsedRange).Offset(1)
    .AutoFilterMode = False
    r.AutoFilter Field:=9, Criteria1:=Array("b001", "b002", "b003"), Operator:=xlFilterValues
    If r.Columns(1).SpecialCells(xlCellTypeVisible).Count > 1 Then r.Copy shT.Range("A1")
    .AutoFilterMode = False
  End With
 
  shT.Select
 
End Sub

【77679】Re:VBAを使い、別シートにデータを抽出し...
お礼  たけちゃんまん  - 15/11/28(土) 0:32 -

引用なし
パスワード
   βさま

迅速にご対応頂き、ありがとうございます!
早速、実行してみたいところではあるのですが、会社PCの会社サーバーでの作業となる為、すぐに結果のご報告が出来ず、申し訳ありません…。
月曜日にフィルターオプション及びオートフィルターの双方を実行させて頂き、結果をご報告させて頂きますので、お時間を頂けますでしょうか。宜しくお願い致します。

βさまにお伺いするのは厚かましいのは承知の上で、一つお願いがございます。

それぞれのコードがどの様な働きをしているのか、ご教授頂きたいのですが…。今回、ご教授頂いたコードについて学習し、これから先の作業に活かせる様にしたいと思っておりますので、宜しくお願い致します。

【77680】Re:VBAを使い、別シートにデータを抽出し...
お礼  たけちゃんまん  - 15/11/28(土) 0:48 -

引用なし
パスワード
   βさま

フィルターオプション並びにオートフィルターについてのお礼が一つになってしまい、申し訳ありません。

excel2010を使用しておりますので、オートフィルターも実行させて頂きます!

【77681】Re:VBAを使い、別シートにデータを抽出し...
発言  β  - 15/11/28(土) 13:48 -

引用なし
パスワード
   ▼たけちゃんまん さん

コードの解説(といってもそんなにたいそうなコードではないのですが)は以下の通りですが
その前に、是非、エクセルの強力な機能であるオートフィルターやフィルターオプションを
シート上の操作で体験して、その便利さを実感してください。
オートフィルターについてはおそらく、経験があるとは思いますが、「オートフィルター」
あるいは「フィルターオプション」で検索して、出てくるページの中でわかりやすいものを参考に
実際にやってみてください。
いずれも、処理効率も、ゴリゴリコードを書いて処理するより、格段に優れています。

フィルターオプションはオートフィルターに比べて、与える条件も細かに設定できますし
また、その場所でフィルタリングの他にフィルタリング結果を別の場所に抽出ということも
その標準機能の中で実現可能で、優れものです。
ただ、条件の設定がちょっと煩雑(?)で、最初は敬遠されがちかも。

いずれにしても、これら操作をマクロ記録しますと、私がアップしたコードが生成されます。

なお、オートフィルターでxl2007以降限定と書きましたが、オートフィルター自体は古くからある機能。
ただ、xl2003までは、抽出対象を2つまでしか与えられなかったのですが、xl2007以降、必要なだけ
与えることができるようになっています。

'フィルターオプション
  
  Application.ScreenUpdating = False

   '処理中の画面の動きを隠します。画面のちらつきを抑止するとともに、
   'セル書き込み時の処理効率をアップさせる効果があります。
  
  Set shT = Sheets("Sheet2") '転記シート

   'コード内で何度か参照しますので、短めの名前の変数に代入して
  '以降は shT を使います。コードが見やすくなる効果があります。

   shT.UsedRange.ClearContents
  
   'UsedRange は、そのシートで使用されている領域を矩形で表したアドレス領域。
   'これから、そのシートに転記するので、その前に、クリアしておきます。

  With Sheets("Sheet1")    '元シート

   '↑で shT に代入するコードがありましたが、もう1つ、オブジェクトを With でくくって
  '以下、End With までの間でそのオブジェクトを参照する場合、.そのオブジェクト という
   '記述ができます。これも、コードを見やすく、すっきりさせる効果があります。

    cols = .UsedRange.Columns.Count

   'UsedRange は使用領域。Sheet1 は A列 から始まっていますので、その列数が転記列数になります。

    Set r = .Range("A1", .UsedRange).Offset(1)

   'Sheet1 のタイトル行は2行目です。わかりにくいかもしれませんが
   '.Range("A1", .UsedRange) は、2行目から始まるリストの領域に1行目を加えた領域になります。
   'で、.Offset(1) は、それを1行下に移動させたところ、つまりリスト領域に、その下の空白行を
   '加えた領域になります。本来、この空白行は不要ですが面倒なので、リスト領域に含めています。
   '1行目が完全に空白行であれば .UsedRange.Offset(1) でいいのですが、そこが不明でしたので
  'あえて このような書き方にしました。

    .Cells(1, cols + 2).Value = .Range("I2").Value '抽出項目タイトル

   'フィルターオプションに与える抽出条件項目名を、リスト領域の外につくります。

    .Cells(2, cols + 2).Resize(3).Value = WorksheetFunction.Transpose(Array("'=b001", "'=b002", "'=b003"))

   'その下、2行目以降に抽出文字列を3つセットしています。たんに b001 といった文字列にしますと
   'b001 からはじまるものすべてが対象になりますので = を付けて完全一致条件にしています。

    r.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=.Cells(1, cols + 2).CurrentRegion, _
                CopyToRange:=shT.Range("A1"), Unique:=False

   'この1行がフィルターオプション実行コードです。抽出結果を SHeet2のA1から始まる領域に転記します。

    .Cells(1, cols + 2).CurrentRegion.Clear

   '処理後、リスト領域の外側に作った条件欄をクリアします。

  shT.Select

   '処理結果が目で見れるように最後に Sheet2をアクティブにします。
  
'オートフィルター

  ★フィルターオプションで説明したコードについては割愛します。

    .AutoFilterMode = False

   '念のため、オートフィルターモードを解除します。

    r.AutoFilter Field:=9, Criteria1:=Array("b001", "b002", "b003"), Operator:=xlFilterValues

   'この1行で、リストのI列に指定の文字列があるものをフィルタリングします。

    If r.Columns(1).SpecialCells(xlCellTypeVisible).Count > 1 Then r.Copy shT.Range("A1")

   '抽出があった場合、タイトル行以外にデータ行がありますので、その状態かどうかを判定し
  '抽出されていれば、オートフィルター領域を Sheet2の A1から始まる場所にコピペします。
   'ここが、オートフィルターの「ミソ」なんですが、抽出されたものだけがコピペ対象になります。

    .AutoFilterMode = False

   '処理後、オートフィルターモードを解除します。

【77685】Re:VBAを使い、別シートにデータを抽出し...
お礼  たけちゃんまん  - 15/11/29(日) 11:54 -

引用なし
パスワード
   βさま

お礼が遅くなり、申し訳ありません。

詳しく解説して下さり、ありがとうございます!
日頃からフィルターは使用しておりますが、ほんの一部しか使いこなせておりませんので、オートフィルター及びフィルターオプションについて、学ばせて頂きます。

明日、実行しながら学習させて頂き、結果をご報告させて頂きます。

【77695】Re:VBAを使い、別シートにデータを抽出し...
発言  β  - 15/11/30(月) 17:57 -

引用なし
パスワード
   ▼たけちゃんまん さん:

もう1つ参考までに。

↑で、オブジェクト(今回はシート)を短めの変数に格納して参照、
あるいは With オブジェクト でくくり、以降 ピリオドをつけて
.オブジェクトとして参照する利点を、コードがすっきりするとコメントしましたが
加えて、重要な利点があります。

たとえばコードで

SHeets("Sheet1").Range(なんたら) と参照すると、コードごとに、
そのシートオブジェクトをさがしに行きます。
一方、変数.Range(なんたら) や .Range(なんたら) と記述すると
シートオブジェクトをピンポイントで直接参照しますので、処理効率がアップします。

893 / 13644 ツリー ←次へ | 前へ→
ページ:  ┃  記事番号:
2610219
(SS)C-BOARD v3.8 is Free