エクセルVBAで不思議な現象が！

質問

エクセル2010です。
理解できない現象で困っています。
Sheets("DATA")の.Range("B1:S1")には、B1が1、C1が2というように1から18までの数値が連番に入っています。S1には18です。
以下のVBAを走らせると、普通はSheets("test")のRange("B26:S26")にも1から18までの数値が連番に入ります。
Sub TEST()
Dim ws As Worksheet
Dim lr As Long
Set ws = ThisWorkbook.Sheets("DATA")
lr = 25
With ThisWorkbook.Sheets("test")
.Range("B1:S1").Offset(lr).Value = ws.Range("B1:S1").Value 'データ転記
End With
End Sub
ところが、このSheets("test")の20行目までデータが入っていて、オートフィルターがかかっているとします。（条件はG列の空白だけ抽出）
そして、さらにC列以降のどれかの列が非表示になっていると、その非表示列以降のセルはすべて1になってしまいます！
なぜ、このような不思議な結果となるのか理解できません。
やむを得ず、オートフィルタをいったん解除し、列を再表示してから転記するようにしていますが、原因がわかりません。
どなたかご教示いただけませんでしょうか？

回答No.7

HohoPapa · ベストアンサー

ごめんなさい、私におおきな誤解がありました。

私は、
フィルターの設定され、非表示列をセットしたシートは
Dataシートだと思っていました。
Testシートだったんですね。
一番最初に指摘された事象を再現できました。

この事象も、
VBAの仕様なのか、都合なのか、制限事項なのか
はたまたバグなのか定かではありませんが

H列が非表示でオートフィルターがオンなので、
おそらく、VBAが
.Range("B1:S1").Offset(lr).Value = ws.Range("B1:S1").Value
を
.Range("B1:G1,I1,J1,K1,L1,M1,N1,O1,P1,Q1,R1,S1").Offset(lr).Value = _
ws.Range("B1:S1").Value
と読み替えているんだろうと思います。

お礼

何度もご丁寧にありがとうございました。再現しましたか！
よかった。
>Range("B1:G1,I1,J1,K1,L1,M1,N1,O1,P1,Q1,R1,S1").Offset(lr).Value = _
ws.Range("B1:S1").Value
と読み替えているんだろうと思います。

それで非表示列の次以降はみなB1の値になるわけですか・・・・
これはバグですよね・・・・。

回答No.9

imogasi

#1,#３です。気になって、改めてやってみました。
結果的に、論点がずれていて、お騒がせしました。すみませんでした。
Sub test01() 'OK
Worksheets("Sheet1").Range("c2:D5").Value = Worksheets("Sheet1").Range("a1:B4").Value
End Sub
Sub test02() 'OK
Worksheets("Sheet1").Range("c2:D5") = Worksheets("Sheet1").Range("a1:B4").Value
End Sub
Sub test03() 'NO
Worksheets("Sheet1").Range("c2:D5").Value = Worksheets("Sheet1").Range("a1:B4")
End Sub
私は .Valueを省く主義でやってきました（test03のやり方）が、うまく行かず、バリアント変数に、一旦代入してしのいでいました。
ーー
左辺右辺とも、 .Valueを付ければうまく行くようです。上記test01。
test02は参考。
複数セルを複数セルに値代入するときは、注意しないといけない、ことが判りました。
ーー
まず目についたので、＃１をあげましたが、上記のことから、質問の原因は他にあるようです。すみませんでした。

お礼

はい、ありがとうございました。

回答No.8

HohoPapa

>VBAの仕様なのか、
>都合なのか、
>制限事項なのか
>バグなのか
我々は、
これらのどれなのか白黒つけることのできる立場にありませんし、
バグ！！！と叫んで改修されるわけでもありません。

フィルターがオンで、非表示列のある場所に
今回のようなコードでデータを埋めるやり方は
無謀と評価されても仕方ないかもしれませんヨ。

個人的には、
意図しない結果になる動作ですから、
VBAが実行時エラーにしてほしいところです。

お礼

そのとおりですね、ありがとうございました。
また、昨年末はNo.9413059で、Spinbuttonのご教示をいただきありがとうございました。

回答No.6

HohoPapa

With ws
　.Range("A1:J1").AutoFilter 'オートフィルタ設定
　.Range("$A$1:$J$10").AutoFilter Field:=6, Criteria1:="$F$8" 'オートフィルタ抽出
　.Columns("E:E").Hidden = True 'H列非表示
　For i = 1 To 10
　　w(i) = i '配列に連番
　Next i
　.Range("A15:J15").Value = w() 'データ貼り付け
End With

このコードの場合、
添付画像のような結果になります。
E15はNullです。

VBAの仕様なのか、都合なのか、制限事項なのか
はたまたバグなのか定かではありませんが
おそらく、
　.Columns("E:E").Hidden = True 'H列非表示
があり、
更にオートフィルターがオンなので

　.Range("A15:J15").Value = w() 'データ貼り付け
を
　.Range("A15:D15,F15:J15").Value = w() 'データ貼り付け
と読み替えているものと思います。
つまり、１つの配列：w()　を
複数の配列："A15:D15,F15:J15"
にそれぞれ埋めているということだろうと。

やはり、繰り返しますが
地道なコードが求められるんだろうと思います。

お礼

ありがとうございました。
>１つの配列：w()　を複数の配列："A15:D15,F15:J15"にそれぞれ埋めている
なあるほど、そういうことでしたか。
下記のコードのように、配列から張り付けと、セル範囲の張り付けを同時にやってみたら結果が違うのでまた悩んでいましたが、おっしゃるように地道なコードでやろうと思います。
何度も何度もありがとうございました。

Sub TEST20180804_3()
　　Dim ws As Worksheet
　　Dim myC As Range
　　Dim i As Long
　　Dim myW(1 To 10) As Long
　　Set ws = Sheets.Add(After:=ActiveSheet) 'TESTシート追加

　　With ws
　　　　.Rows("1:5").HorizontalAlignment = xlCenter
　　　　For Each myC In .Range("A1:J1")
　　　　　　myC.Value = myC.Column 'TESTデータ項目入力
　　　　Next myC
　　　　.Range("L1:U1").Value = .Range("A1:J1").Value
　　　　For Each myC In .Range("A2:J5")
　　　　　　myC.Value = myC.Address(0, 0) 'TESTデータ範囲にデータ入力
　　　　Next myC
　　　　.Range("A1:J1").AutoFilter 'オートフィルタ設定
　　　　.Range("$A$1:$J$5").AutoFilter Field:=6, Criteria1:="F4" 'オートフィルタ抽出
　　　　.Columns("G:G").Hidden = True 'G列非表示
　　　　For i = 1 To 10
　　　　　　myW(i) = i '配列に連番
　　　　　　.Cells(1, i + 11).Value = i '.Range("L1:U1")に連番
　　　　Next i
　　　　.Range("A10:J10").Value = myW() '配列データ貼り付け
　　　　.Range("A11:J11").Value = .Range("L1:U1").Value 'シートデータ転記
　　End With
End Sub

回答No.5

HohoPapa

>同様にフィルタ＆非表示列があると、
>以下のコードで行のデータを削除しても非表示列内のデータは消せません。なぜでしょう？
>Sub TEST06()
>　With ThisWorkbook.Sheets("test")
>　　.Rows("26").ClearContents
>　End With
>End Sub

フィルターが設定されている状態で
（VBAによらず、）
任意行、任意行範囲、任意の複数行を選択して
Deleteキーを押したときには
非表示行、非表示列は消さないことから、
VBAもそれを踏襲しているものと思います。

私だったら
VBAでシート上のデータを扱う場合は
実行直前にオフとし
実行直後にオンとする制御をします。

利用形態次第では、
手作業で比較的複雑なフィルターを設定しているケースが
あると思います。
そのような場合は、
セル範囲や列、行といった複数セルをまとめて扱う処理は避け、
地道に１セルごとの処理を繰り返すコードのほうが無難と思います。

お礼

>そのような場合は、
>セル範囲や列、行といった複数セルをまとめて扱う処理は避け、
>地道に１セルごとの処理を繰り返すコードのほうが無難と思います。

はい、原因がわからない以上そうするしかないですね。
それにしても、HohoPapaさんの端末では再現されず、わたしの2
台（会社の2010と自宅の2016）ではそうなることが不思議です。
ありがとうございました。

補足

すみません、これを試していただけませんでしょうか？
セル範囲からの転記ではなく配列データを貼ってみました。
これでも非表示列以降、データがおかしくなります。
これなら再現しませんか？
Sub TEST20180804()
　　Dim ws As Worksheet
　　Dim c As Range
　　Dim i As Long
　　Dim w(1 To 10) As Long
　　Set ws = Sheets.Add(After:=ActiveSheet) 'TESTシート追加
　　For Each c In ws.Range("A1:J10")
　　　　c.Value = c.Address 'TESTデータ範囲にデータ入力
　　Next c
　　With ws
　　　　.Range("A1:J1").AutoFilter 'オートフィルタ設定
　　　　.Range("$A$1:$J$10").AutoFilter Field:=6, Criteria1:="$F$8" 'オートフィルタ抽出
　　　　.Columns("E:E").Hidden = True 'H列非表示
　　　　For i = 1 To 10
　　　　　　w(i) = i '配列に連番
　　　　Next i
　　　　.Range("A15:J15").Value = w() 'データ貼り付け
　　End With
End Sub

回答No.4

HohoPapa

再現できないですね～

参考に画像を添付しますので
再現手順を再確認し、
>新たなBookを用意し
>質問文に書かれたことだけを行っても再現する
かどうか、確認してみてください。

ところで、
マクロを実行する直前、
dataシートのL1セルには、期待通り11が埋まっているんですよね？

もし私が同じ事態に陥るなら
次のようなコードで回避します。
このコードも確認してみてください。

Sub Sample()
　Const lr = 25
　Dim wsI As Worksheet
　Dim wsO As Worksheet
　Dim ColCounter As Long

　Set wsI = ThisWorkbook.Sheets("Data")
　Set wsO = ThisWorkbook.Sheets("Test")

　For ColCounter = 2 To 19
　　wsO.Cells(1 + lr, ColCounter).Value = _
　　　wsI.Cells(1, ColCounter).Value
　Next ColCounter
End Sub

お礼

何度もありがとうございます。
再現しませんか・・・・
ご教示のSub Sample()は、回答02の補足にわたしが書いたSub TEST05()とおなじようなことですよね。これは正しく転記出来ました。

回答No.3

imogasi

#2です。
補足の点から、
.Valueを付ける方がよいのは判ります。しかしその点ではなくて、Value（セルの値）に限ってでも
複数セル範囲＝複数セル範囲（Range（・・）＝Range(・・))、のような書き方は、可能ですか？
右辺のセルの値を、左辺のセルに、複写（的に）実行されますか？
それが出来たらよいのに、といつも思っていますが、うまく行かないので、一旦バリアント変数を介して、左辺の、複数セル範囲に、代入しています。
回答文章とコードも、そのテストをして、確認したつもりなんです。
ーー
うまく行くようなら、その点で、私の無知・誤解ですので、うまく行くと、補足で、教えてください。
ーー
ただし、上記だけが質問のうまく行かない原因ではないかもしれませんが。

お礼

何度もありがとうございます。
Sub TEST04()
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("DATA")
With ThisWorkbook.Sheets("test")
.Range("B26:S26").Value = ws.Range("B1:S1").Value 'データ転記
End With
End Sub
これは、転記先シートにオートフィルターで搾りこまれてなく、非表示にされた列がなければ問題なく作動します。

回答No.2

HohoPapa

少なくとも私の環境（Office2016）では
>その非表示列以降のセルはすべて1になってしまいます！
という現象を再現することができませんし、
見たところ、コードに怪しいところはないだろうと思います。

単にセル間でセル値を複写しているだけなのに、
>1になってしまいます！
1が埋まるのはいかにも解せません。

新たなBookを用意し
質問文に書かれたことだけを行っても再現するようなら
VBAのバグを疑ってもいいだろうと思います。

以下、質問文を読み、再現できるかどうか確かめるときに
迷ったことを書きますので、可能なら説明してください。

>このSheets("test")の20行目までデータが入っていて
何列目まで埋まっていますか？

>オートフィルターがかかっているとします。
この範囲は？
つまり、
単にA1セルが選択された状態でオートフィルターを選択しただけなのか？
言い換えると、
オートフィルターは任意な範囲で設定可能だということです。

>さらにC列以降のどれかの列が非表示になっていると
これはどちら側のシート？
また、再現手順の説明でしょうから
Ｄ列とかＥ列とか限定してしまいましょう。

.Range("B1:S1").Offset(lr).Value = ws.Range("B1:S1").Value 'データ転記
これを
.Range("B26:S26").Value = ws.Range("B1:S1").Value 'データ転記
としたときの結果は？

再現手順に
オートフィルターと列の非表示の双方が登場しますが
一方だけなら再現しないのか？

オートフィルターに代えて、任意行を非表示にしても再現するのか？

お礼

HohoPapaさん、あリがとうございます。
いま、もう1台のパソコンの2016でも試しましたが同じ結果です。

>1が埋まるのはいかにも解せません。
今回の例では1でしたが、Sheets("DATA")のRange("B1")にAをいれるとAで埋まります。つまり非表示列の次のセル以降がSheets("DATA").Range("B1")の値で埋まります。なお、非表示にした列のセルには何も転記されませんでした。

>何列目まで埋まっていますか？
今回の例ではデータは17行目までです。

>この範囲は？
A列からS列までです。1行目にオートフィルターを設置し、D列のフィルタのみ絞り込みをしてみました。

>これはどちら側のシート？
非表示列があるのはSheets("test")です。H列を非表示としました。

>.Range("B1:S1").Offset(lr).Value = ws.Range("B1:S1").Value 'データ転記
>これを
>.Range("B26:S26").Value = ws.Range("B1:S1").Value 'データ転記
>としたときの結果は？
いま試しましたが同じでした。

>再現手順にオートフィルターと列の非表示の双方が登場しますが
>一方だけなら再現しないのか？
はい、両方の条件が重なったときです。

>オートフィルターに代えて、任意行を非表示にしても再現するのか？
ためしにフィルター抽出をやめ、行の非表示をして見ましたが再現しません。

補足

とりあえずは以下のようにいったん配列に入れて、1つづつ転記することで解決しました。
Sub TEST05()
　　Dim ws As Worksheet
　　Dim lr As Long
　　Dim d As Variant
　　Set ws = ThisWorkbook.Sheets("DATA")
　　lr = 25
　　d = ws.Range("B1:S1")
　　With ThisWorkbook.Sheets("test")
　　　　For n = 1 To 18
　　　　　　.Cells(lr + 1, n + 1).Value = d(1, n) 'データ転記
　　　　Next n
　　End With
End Sub
ただ、なぜこんな現象が起きるのか理解できず、困惑しています。
また、同様にフィルタ＆非表示列があると、以下のコードで行のデータを削除しても非表示列内のデータは消せません。なぜでしょう？
Sub TEST06()
With ThisWorkbook.Sheets("test")
.Rows("26").ClearContents
End With
End Sub
わかりましたらご教示ください。

回答No.1

imogasi

例えば、F3:H5の各セルに
12 3 11
1 4 22
3 5 33
とデータを入れる。
ーー
標準モジュールに
Sub test01()
Range("a1:C3") = Range("f3:H5")
End Sub
Sub test02()
Dim d As Variant
d = Range("f3:H5")
Range("a1:C3") = d
End Sub
を作って
test01を実行してもA1:C3には何も起こらない。
test02を実行すると、A1:C3は
12 3 11
1 4 22
3 5 33
となった。
test01と同じこと（考え）を
.Range("B1:S1").Offset(lr).Value = ws.Range("B1:S1").Value
で（できると）考えてやってませんか。

お礼

早速ありがとうございます。

Sub TEST03()
Dim ws As Worksheet
Dim lr As Long
Dim d As Variant
Set ws = ThisWorkbook.Sheets("DATA")
lr = 25
d = ws.Range("B1:S1")
With ThisWorkbook.Sheets("test")
.Range("B1:S1").Offset(lr).Value = d 'データ転記
End With
End Sub

としてみましたが、結果は同じでした。

補足

TEST01は
ふつうはRange("a1:C3") = Range("f3:H5")じゃなく
Range("a1:C3").Value = Range("f3:H5").Valueと書きますよね？
で、Range("a1:C3").Value = Range("f3:H5").Valueとすると test02と同じ結果になります。