[[20110607215309]]　『オートフィルタの可視セルのみ移動しコピペしたい』（バーバラ）　ページの最後に飛ぶ

 『オートフィルタの可視セルのみ移動しコピペしたい』（バーバラ）
 エクセル２００７、ＷｉｎＸＰです。
 また、お世話になります。
 A1:B10に次のようなリストがあります。
 項目1	項目2
 X1	0
 X2	0
 X3	10
 X4	0
 X5	0
 X6	5
 X7	0
 X8	4
 X9	3

 これについて、B列で0以外を表示でオートフィルタかけます。	
 項目1	項目2
 X3	10
 X6	5
 X8	4
 X9	3

 この状態で、B列を上から順に値をコピーして、３列右のセルにコピーする
 というマクロを作成したいと考えています。
 ここで、オートフィルタかけたセルのみを動く、というところを
 どのように書けばいいのかご教授いただけないでしょうか。

 >３列右のセルにコピーする
 つまり、E列？

 要件誤解してたスルーを。
 エラーチェックはしていない。

 Sub Sample()
    Intersect(ActiveSheet.AutoFilter.Range, ActiveSheet.AutoFilter.Range.Offset(1)).Columns(2).SpecialCells(xlCellTypeVisible).Copy
    ActiveSheet.AutoFilterMode = False
    Range("E2").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
 End Sub

 ↑　あぁ、きっと勘違いだ。　もとの行と同じ行にという意味だよね。（追記）

 ぶらっと立ち寄り

 きっと、こっちだ。

 Sub Sample2()
    Dim r1 As Range
    Dim r2 As Range
    Dim c As Range
    Set r1 = Intersect(ActiveSheet.AutoFilter.Range, ActiveSheet.AutoFilter.Range.Offset(1)).Columns(2).SpecialCells(xlCellTypeVisible)
    For Each r2 In r1.Areas
        For Each c In r2
            c.Offset(, 3).Value = c.Value
        Next
    Next
    Set r1 = Nothing
    Set r2 = Nothing
 End Sub

 ぶらっと立ち寄り@もう寝るべ

 朝起きて見直したら以下でもよかった。睡魔がある状態でコード書いちゃいけないね。

 Sub Sample3()
    Dim r1 As Range
     Dim c As Range
    Set r1 = Intersect(ActiveSheet.AutoFilter.Range, ActiveSheet.AutoFilter.Range.Offset(1)).Columns(2).SpecialCells(xlCellTypeVisible)
    For Each c In r1
        c.Offset(, 3).Value = c.Value
    Next
    Set r1 = Nothing
  End Sub

 ぶらっと立ち寄り

 ぶらっと立ち寄りさん
 確認しました。思い通りに動きました。ありがとうございました。
 思った以上にすっきりしていて驚きました。
 Sample3について初めて出てくるコードが多く、わからないことだらけなので、
 教えていただきたいです。

 1.Offset(1)は、今回の例だとOffset(1,0)を意味するようです。
  理由は、Offset(1,0)に書き換えたらうまくいきましたが、
　Offset(0,1)に書き換えたらうまくいかなかったため、そう判断しました。
　なぜ、引数を省略しているのに、エクセルがOffset(0,1)と認識しないのでしょうか。
　基本的な私の認識が間違っていますか？

 2.Intersectメソッドは、２つの範囲が共有している範囲を返す、ようです。

 引数のうち、ActiveSheet.AutoFilter.Rangeは、オートフィルタ状態の可視セルA1:B10を指定し、
 ActiveSheet.AutoFilter.Range.Offset(1)は、オートフィルタ状態の可視セルA4:B11を指定する。
 各々共有する可視セルA4:B10のうち、２列目（B列）のセルを上から順番に、３列右のセルに
 コピーする、コードという理解でよろしいでしょうか。
 （バーバラ）

 以下メモ。

 セル領域.Offset(0から始まる行変位,0から始まる列変位) で列変位、行変位は省略可能。省略時の解釈値は 0 。
 Offset(n) は列変位が省略されている形、Offset(,n) は行変位が省略されている形。
 今回のOffset(1) は、２番目の引数「列変位」が省略されているので　Offset(1,0) とみなされる。

 Intersect(領域1,領域2,領域3,・・・・・) と、任意の数の領域の指定が可能。
 実際には、２つの領域のANDをとるために使用されることが多い。
 （似たようなメソッドで　Union。これは複数の領域のORをとる）

 シート.AutoFilter.Range
 オートフィルターが相手にしているタイトル行を含んだリスト領域。
 今回の例では、理解されたとおり、A1:B10。可視かどうかは関係がない。

 シート.AutoFilter.Range.Offset(1)
 オートフィルターのリスト領域を１つ下に移動させた領域。
 これとオートフィルターリスト領域とのAND(Intersect)は、タイトル行を除いた
 リストのボディ部分の領域。

 一口メモ）たとえば１行目にタイトル行があるシートのタイトルのみ残して、残りをクリアする場合、
 　 　　Intersect(シート.Usedrange,シート.UsedRange.Offset(1)).ClearContents と書くことができる。

 Set r1 = Intersect(ActiveSheet.AutoFilter.Range, ActiveSheet.AutoFilter.Range.Offset(1)).Columns(2).SpecialCells(xlCellTypeVisible)

 １行で書いたけど、分解すると

 ①Intersect(ActiveSheet.AutoFilter.Range, ActiveSheet.AutoFilter.Range.Offset(1))

   リストのボディ領域。つまり　"A2:B10"（上でも書いたけど、可視かどうかは関係がない。）

 ②.Columns(2)

   ①の中の２番目の列。つまり、"B2:B10"

 ③.SpecialCells(xlCellTypeVisible)

   ②の中の可視領域（ここではじめて可視が登場）つまり　"B4,B7,B9:B10"

 この③からセルを取り出しているのが

 For Each c In r1
     c.Offset(, 3).Value = c.Value
 Next

 このr1が③。③からセルを１つずつ、cというRangeObject変数に取り出している。

 c.Offset(, 3).Value = c.Value

 取り出したc (B列の可視セル）の値を、Offset(,3) つまり同じ行の３列右のセルに転記。

 ぶらっと立ち寄り

 追伸

 今回アップしたコードは実は手抜きコード。
 Set 変数 = Intersect(・・・・) で、共通の領域がないとき、変数には「何もセットされない」
 したがって、ちゃんとしたコードを書くなら、実行前に変数をNothingにしておき（Dimで初期値はNothing)
 実行後、If Not 変数 Is Nothig Then と判定を入れて処理必要。

 また、SpecialCells(セルの条件) で領域を取得しようとした場合、条件のセルがないと、「実行時エラー」となる。
 したがって、ここも、手をぬかずに書くなら、

 事前にNothingにしておいた上で、
 On Error Resume Next (エラーが発生しても中断せず、次のコードを実行）
 SpecialCellsによる領域取得
 On Error GoTo 0 (エラーをスキップする対応は終了）
 としておいて、やはり、変数がNothingかどうかの判定を行う。

 ぶらっと立ち寄り

 ぶらっと立ち寄りさん
 解説ありがとうございます。
 Offset(1)が、ボディ部分を特定する処理であることに気づくことができました！
 （バーバラ）

コメント返信：

[ 一覧(最新更新順) ]

[[20110607215309]] 『オートフィルタの可視セルのみ移動しコピペしたい』（バーバラ） ページの最後に飛ぶ

[[20110607215309]]　『オートフィルタの可視セルのみ移動しコピペしたい』（バーバラ）　ページの最後に飛ぶ