Excel VBA質問箱 IV

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

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

  新規投稿 ┃ツリー表示 ┃一覧表示 ┃トピック表示 ┃検索 ┃設定 ┃過去ログ ┃ホーム  
931 / 13645 ﾂﾘｰ ←次へ　｜　前へ→
▼ 【77443】多量の複数セルをコピーし入れ替えして貼り付け YUKI 15/10/7(水) 14:18 質問[未読]
【77444】Re:多量の複数セルをコピーし入れ替えして... ウッシ 15/10/7(水) 15:12 回答[未読]
【77445】Re:多量の複数セルをコピーし入れ替えして... ichinose 15/10/8(木) 7:03 発言[未読]
【77446】Re:多量の複数セルをコピーし入れ替えして... カリーニン 15/10/8(木) 10:02 発言[未読]
【77447】Re:多量の複数セルをコピーし入れ替えして... YUKI 15/10/8(木) 13:23 お礼[未読]
【77448】Re:多量の複数セルをコピーし入れ替えして... カリーニン 15/10/8(木) 17:53 発言[未読]
【77450】Re:多量の複数セルをコピーし入れ替えして... ichinose 15/10/9(金) 7:05 発言[未読]
【77451】Re:多量の複数セルをコピーし入れ替えして... β 15/10/9(金) 9:08 発言[未読]
【77443】多量の複数セルをコピーし入れ替えして貼...
質問  YUKI  - 15/10/7(水) 14:18 -
引用なし
パスワード
   再度行き詰ってしまったのでお願いします。

別ブックから該当の複数セルを選択し、行列を入れ替えて値貼付を行いたいです。
コピーの対象はA〜OH列の複数行です。

少量の時はうまく行っていたのですが、選択数が増えたら
'Range'メソッドは失敗しました：'_global'オブジェクト
とのエラーが起きてしまいました。
出来れば行修正の間違いの少ない記述が知りたいのですが
お教え願えませんでしょうか


Range("A1:OH4,A11:OH11,A13:OH14,A18:OH18,A25:OH25,A31:OH31,A33:OH33,A35:OH35,A61:OH61,A64:OH65,A67:OH67,A71:OH72,A84:OH84,
A88:OH88,A90:OH90,A104:OH104,A107:OH108,A110:OH110,A114:OH114,A132:OH133,A151:OH151,A157:OH157,A160:OH160,A167:OH167,
A175:OH175,A184:OH184,A211:OH211,A205:OH205").Copy
　　ThisWorkbook.Activate
　　Sheets("OTHER").Select
　　Range("A2").PasteSpecial Paste:=xlPasteValues, Transpose:=True
【77444】Re:多量の複数セルをコピーし入れ替えし...
回答  ウッシ  - 15/10/7(水) 15:12 -
引用なし
パスワード
   こんにちは

Sub test1()
　　Intersect(Range("A:OH"), _
　　　　Union(Range("A1:A4,A11,A13:A14,A18,A25,A31,A33,A35,A61"), _
　　　　　　Range("A64:A65,A67,A71:A72,A84,A88,A90,A104,A107:A108,A110"), _
　　　　　　Range("A114,A132:A133,A151,A157,A160,A167,A175,A184,A205,A211")).EntireRow).Copy

　　ThisWorkbook.Sheets("OTHER").Range("A2").PasteSpecial Paste:=xlPasteValues, Transpose:=True

End Sub

とか、色々出来ますよ。


▼YUKI さん：
>再度行き詰ってしまったのでお願いします。
>
>別ブックから該当の複数セルを選択し、行列を入れ替えて値貼付を行いたいです。
>コピーの対象はA〜OH列の複数行です。
>
>少量の時はうまく行っていたのですが、選択数が増えたら
>'Range'メソッドは失敗しました：'_global'オブジェクト
>とのエラーが起きてしまいました。
>出来れば行修正の間違いの少ない記述が知りたいのですが
>お教え願えませんでしょうか
>
>
>Range("A1:OH4,A11:OH11,A13:OH14,A18:OH18,A25:OH25,A31:OH31,A33:OH33,A35:OH35,A61:OH61,A64:OH65,A67:OH67,A71:OH72,A84:OH84,
>A88:OH88,A90:OH90,A104:OH104,A107:OH108,A110:OH110,A114:OH114,A132:OH133,A151:OH151,A157:OH157,A160:OH160,A167:OH167,
>A175:OH175,A184:OH184,A211:OH211,A205:OH205").Copy
>　　ThisWorkbook.Activate
>　　Sheets("OTHER").Select
>　　Range("A2").PasteSpecial Paste:=xlPasteValues, Transpose:=True
【77445】Re:多量の複数セルをコピーし入れ替えし...
発言  ichinose  - 15/10/8(木) 7:03 -
引用なし
パスワード
   >Range("A1:OH4,A11:OH11,A13:OH14,A18:OH18,A25:OH25,A31:OH31,A33:OH33,A35:OH35,A61:OH61,A64:OH65,A67:OH67,A71:OH72,A84:OH84,
>A88:OH88,A90:OH90,A104:OH104,A107:OH108,A110:OH110,A114:OH114,A132:OH133,A151:OH151,A157:OH157,A160:OH160,A167:OH167,
>A175:OH175,A184:OH184,A211:OH211,A205:OH205").Copy
>　　ThisWorkbook.Activate
>　　Sheets("OTHER").Select
>　　Range("A2").PasteSpecial Paste:=xlPasteValues, Transpose:=True
Sub test()
　　Dim add As String
　　add = "A1:OH4,A11:OH11,A13:OH14,A18:OH18,A25:OH25,A31:OH31,A33:OH33,A35:OH35,A61:OH61,A64:OH65,A67:OH67,A71:OH72,A84:OH84,A88: OH88,A90:OH90,A104:OH104,A107:OH108,A110:OH110,A114:OH114,A132:OH133,A151:OH151,A157:OH157,A160:OH160,A167:OH167,A175:OH175,A184:OH184, A211:OH211,A205:OH205"
　　MsgBox Len(add)
　　Range(add).Copy
End Sub


セルアドレス文字列長が255を超えると、
range("xxx")は、提示されたようなエラーに成ります。

この255を意識したコードにしなければなりません。

かと言って、単純に255で切るって訳には行きませんしね！！

既に提示されているようにUnionメソッドは、有効方法ですが、
これとて、セルアドレスが多くなると、処理速度が、極端に落ちますから、
注意が必要です。


www.vbalab.net/vbaqa/c-board.cgi?cmd=one;no=54;id=FAQ

↑目安箱より、「トロイぞUnion」
【77446】Re:多量の複数セルをコピーし入れ替えし...
発言  カリーニン  - 15/10/8(木) 10:02 -
引用なし
パスワード
   試してませんが、セルに名前を定義して、その名前を使う、というのはどうでしょう？

アイデアは私のオリジナルではなく、最近、他サイトで行継続文字についての質問が
あり、そこで提示されていたアイデアです。
【77447】Re:多量の複数セルをコピーし入れ替えし...
お礼  YUKI  - 15/10/8(木) 13:23 -
引用なし
パスワード
   皆様返答ありがとうございます！

ウッシ様のコードで動くようになりました。ありがとうございます。
Unionは知りませんでした。。。まだまだ勉強不足です。

セルの名前の意義は、
コピー元のブックやシートが異なったりしていても使えるんでしょうか？
名前の定義はグラフの自動更新程度でしか使った事がなかったです。
色々試して勉強します。

ありがとうございました
【77448】Re:多量の複数セルをコピーし入れ替えし...
発言  カリーニン  - 15/10/8(木) 17:53 -
引用なし
パスワード
   他のブックでしたか。見落としてました。
他のブックの名前の定義のセルアドレスの取得方法です。

Dim wbA As Workbook
Dim nm As Name
　Set wbA = Workbooks.Open("C:\Users\USER\Desktop\1746.xlsm")
　Set nm = wbA.Names("aiu")
　MsgBox Range(nm).Address
　Set wbA = Nothing
【77450】Re:多量の複数セルをコピーし入れ替えし...
発言  ichinose  - 15/10/9(金) 7:05 -
引用なし
パスワード
   ▼カリーニン さん：
>試してませんが、セルに名前を定義して、その名前を使う、というのはどうでしょう？
ありがとうございます。

新発見でした。これだとかなり、大きいセル範囲が取得できますね！！

が、これでも限界はありそうです。


Sub test1()
　　Dim rng As Range
　　Dim add As String
　　Dim st As Double
　　Dim idx As Long, jdx As Long
　　st = Now()
　　add = "=" & Cells(1, 1).Address(False, False)
　　For idx = 1 To 44 Step 2
　　　 For jdx = 3 To 20 Step 2
　　　　　add = add & "," & Cells(idx, jdx).Address(False, False)
　　　　　Next
　　　 Next
　　Debug.Print Len(add)
　　Names.add Name:="aaaa", RefersTo:=add
　　Range("aaaa").Select
　　MsgBox Format(Now() - st, "hh:mm:ss")
End Sub


>For idx = 1 To 44 Step 2

Excel2010で　idxが　44だと正常に処理できますが、45では、Names.addの箇所でエラーに成りました。

これを使えば　長いセル範囲（セルアドレス）を操作したい時に　Unionの回数は減らせそうです
【77451】Re:多量の複数セルをコピーし入れ替えし...
発言  β  - 15/10/9(金) 9:08 -
引用なし
パスワード
   ▼YUKI さん：

思い付きですが。

Private Function GetRange(adr As String) As Range
　　Dim r As Range
　　Dim w As Variant
　　Dim a As Variant
　　Dim s As String
　　w = Split(adr, ",")
　　
　　For Each a In w
　　　　If Len(s) = 0 Then
　　　　　　s = a
　　　　Else
　　　　　　If Len(s) + Len(a) > 254 Then
　　　　　　　　AddRange r, Range(s)
　　　　　　　　s = a
　　　　　　Else
　　　　　　　　s = s & "," & a
　　　　　　End If
　　　　End If
　　Next
　　
　　AddRange r, Range(s)
　　Set GetRange = r
　　
End Function

Private Sub AddRange(r As Range, c As Range)
　　If r Is Nothing Then
　　　　Set r = c
　　Else
　　　　Set r = Union(r, c)
　　End If
End Sub

こんなサブプロシジャを準備しておいて、使う際には、アドレス文字列を
何の心配もなく（？）好きなだけ長いもので指定。
サブプロシジャ内で、できるだけ少ないUnion回数でUnionして返すというやりかたも
あるかもしれませんね。

Sub Test()
　　Dim r As Range
　　Dim s As String
　　
　　s = "A1:OH4,A11:OH11,A13:OH14,A18:OH18,A25:OH25,A31:OH31,A33:OH33,A35:OH35,A61:OH61,A64:OH65,A67:OH67,A71:OH72,A84:OH84," & _
　　　　"A88:OH88,A90:OH90,A104:OH104,A107:OH108,A110:OH110,A114:OH114,A132:OH133,A151:OH151,A157:OH157,A160:OH160,A167:OH167," & _
　　　　"A175:OH175,A184:OH184,A211:OH211,A205:OH205"

　　Set r = GetRange(s)
　　
　　MsgBox r.Address
　　r.Select
　　
End Sub

MsgBoxでは長すぎる文字列の表示がし切れませんので、Selectで結果を確認してください。
  新規投稿 ┃ツリー表示 ┃一覧表示 ┃トピック表示 ┃検索 ┃設定 ┃過去ログ ┃ホーム  
931 / 13645 ﾂﾘｰ ←次へ　｜　前へ→
ページ：  ┃  記事番号：
2610219
(SS)C-BOARD v3.8 is Free