エクセル2003のVBAで列を指定

質問

エクセルで特定の列の2～10行目に対して、ある作業をする場合、列を指定する方法は以下のどれがいいでしょうか？あるいはもっといい方法があれば教えてください。
実際には列は約40列（固定）、行は1～2万行（変動）程度で、作業はもっと複雑です。

Sub test01()
Dim col
Dim i As Long, n As Long
For Each col In Array(1, 3, 7, 8, 11) '列番号で指定
For i = 2 To 10
n = n + 1
Cells(i, col).Value = n
Next i
Next col
End Sub

Sub test02()
Dim col
Dim i As Long, n As Long
For Each col In Array("A", "C", "G", "H", "K") '列の記号で指定
For i = 2 To 10
n = n + 1
Cells(i, col).Value = n
Next i
Next col
End Sub

Sub test03()
Dim col
Dim i As Long, n As Long
For Each col In Range("A2,C2,G2,H2,K2") 'セルで指定
For i = 2 To 10
n = n + 1
col.Offset(i - 2).Value = n
Next i
Next col
End Sub

回答No.6

cj_mover · ベストアンサー

こんにちは、
レスをありがとうございます。
◆◆◆
＞arrMatrix(i) = mtxTemplate
＞これって、一次元配列の要素に二次元配列をいれているのでしょうか？
はい、その通りです。
サイズ（行数と列数）を定義済みの空の二次元配列（mtxTemplate）を
一次元配列（arrMatrix）の要素に代入しています。
◆◆◆
あれこれ書き過ぎたみたいなので、少し整理しますが、
配列を使うかどうかは二の次三の次でして、
（１）処理対象のセル範囲（過不足なく）全体を先に取得しておく事
（２）処理対象のセル範囲を Areas で捉え、Area 毎に処理する事
の２点が私のレスのメインテーマです。
（ただひとつ testJ だけは↑（２）と無関係ですが）
処理の選択肢が増え、効率的になること、を示したかったということです。
その為の方法として参照文字列や参照演算子を見直してみてはどうか
という提案も書きました。
◆◆◆
＞実際には...作業はもっと複雑です。
無理に汎用性を持たせようとして却って、
解り難い書き方をしてしまった No.5 の testM ですが
"云われたことだけを決打ち"的に実現するなら二段階配列なしで、
もっとシンプルに書けますので一応補足として掲げておきます。
重要なのはあくまでも Areas プロパティの使い方、その可能性
であることに変わりはありません。
◆◆◆
Sub test_() ' 7058428okg
' ' 列の変更はここで指定　↓"
Const S_REF = "(A:A,C:C,G:G,H:H,K:K,M:M,O:O,S:S,T:T,W:W,Y:Y,AA:AA,AE:AE,AF:AF,AI:AI,AK:AK,AM:AM,AQ:AQ,AR:AR,AU:AU,AW:AW,AY:AY,BC:BC,BD:BD,BG:BG,BI:BI,BK:BK,BO:BO,BP:BP,BS:BS,BU:BU,BW:BW,CA:CA,CB:CB,CE:CE,CG:CG,CI:CI,CM:CM,CN:CN,CQ:CQ) 2:20001"
　　Dim nMtx() As Long
　　Dim r As Range
　　Dim nTop As Long, nBottom As Long
　　Dim n As Long, i As Long
　　' ' 先頭行位置を求める
　　nTop = Val(Mid(S_REF, InStrRev(S_REF, " ") + 1))
　　' ' 末尾行位置を求める
　　nBottom = Val(Mid(S_REF, InStrRev(S_REF, ":") + 1))
　　' ' 二次元配列のサイズ（先頭行から末尾行＊１列）を定義
　　ReDim nMtx(nTop To nBottom, 0) As Long
　　' ' 対象セル範囲ををAreasで捉えてArea毎に処理
　　For Each r In Range(S_REF).Areas
　　　　' ' Area毎、二次元配列に数値をセット
　　　　For i = nTop To nBottom
　　　　　　n = n + 1 ' カウンタ
　　　　　　nMtx(i, 0) = n ' 数値を二次元配列にセット
　　　　Next i
　　　　' ' それぞれのAreaに、二次元配列の数値をセット
　　　　r.Value = nMtx
　　Next r
　　Erase nMtx ' メモリ初期化
End Sub

お礼

> レスをありがとうございます。

ありがとうだなんて・・・。
お礼を言わなければいけないのは私のほうです。
cj_moverさん、ほんとに丁寧に有難うございます。
今回の回答を見て、やっと前回のが理解できたような気がします。
物覚えの悪いemaxemaxをご指導いただき感謝感激です。
これからもよろしくお願い申し上げます。

回答No.5

cj_mover

こんにちは
一応参考として配列版を掲げておきます（コメント抜きです）。
仕様が定まりませんからカスタマイズし易い書き方で、内容的には叩き台レベルです。
testC と testJ の手法を組合せたものに加えてＶＢＡならでは配列の使いこなしがポイントです。
基本技術の組合わせだけで特に変わったことはしていませんが、testJ と見比べると字数の多さ際立ってますよね。
処理の速さ（軽さ）に特化するとしても、結局メンテナンスをどうするか課題は残る訳で、、、。
こんなのもあるって紹介程度のつもりですので、興味なければスルーして下さい。

Sub testM() ' 7058428okg
　　' 列の変更はここで指定　↓"
　　Const S_REF_COLHEAD = "A2,C2,G2,H2,K2,M2,O2,S2,T2,W2,Y2,AA2,AE2,AF2,AI2,AK2,AM2,AQ2,AR2,AU2,AW2,AY2,BC2,BD2,BG2,BI2,BK2,BO2,BP2,BS2,BU2,BW2,CA2,CB2,CE2,CG2,CI2,CM2,CN2,CQ2"

　　Dim arrMatrix As Variant
　　Dim mtxTemplate As Variant
　　Dim areasTarget As Areas
　　Dim r As Range ' ループ用
　　Dim sRngReference As String
　　Dim nTopRow As Long
　　Dim nBottomRow As Long
　　Dim nAreasCount As Long
　　Dim n As Long ' カウンタ
　　Dim i As Long, j As Long ' ループ用

　　nTopRow = 2
　　nBottomRow = 20000
　　ReDim mtxTemplate(nTopRow To nBottomRow, 0)

　　sRngReference = Range(S_REF_COLHEAD).EntireColumn.Address(False, False)
　　sRngReference = "(" & sRngReference & ") " & CStr(nTopRow) & ":" & CStr(nBottomRow)
　　Set areasTarget = Range(sRngReference).Areas

　　nAreasCount = areasTarget.Count
　　ReDim arrMatrix(1 To nAreasCount)

　　For i = 1 To nAreasCount
　　　　arrMatrix(i) = mtxTemplate
　　　　For j = nTopRow To nBottomRow
　　　　　　n = n + 1
　　　　　　arrMatrix(i)(j, 0) = n
　　　　Next j
　　Next i
　　Erase mtxTemplate

　　n = 0
　　For Each r In areasTarget
　　　　n = n + 1
　　　　r.Value = arrMatrix(n)
　　Next r
　　Erase arrMatrix
　　Set areasTarget = Nothing
End Sub

お礼

なんどもありがとうございます。
40列2万行で6秒少々と驚異的な早さでした！

arrMatrix(i) = mtxTemplate
これって、一次元配列の要素に二次元配列をいれているのでしょうか？
残念ながら私の理解力の限界を超えているようです。
もっともっと勉強してから挑戦しようと思います。

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

回答No.4

cj_mover

回答No.3ですが、すみません、転記ミスがありました。
Sub testC()
の
　　Dim rng As Areas ' 対象セルのある列全体をAreas指定。
は削除洩れ、不要となった行です。
失礼しました

お礼

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

回答No.3

cj_mover

お邪魔します。
個人的にはtest03に一票です。
◆◆◆
回答No.1のhallo-2007さんが仰る様に、人それぞれですし、
また、何を優先させるか、、、
例えば、
　・簡潔な（文字数も少なめな）記述
　・解り易い（誰でも解る）記述
　・処理の速さ（軽さ）に特化した記述
　・（一度だけの処理ならば）作業全体が早く終わる（自分が短時間で書けるそこそこの）記述
　・配布に耐えられる（総合的に高い完成度を指向した）記述
皆、状況によって自然に書き分けるものでしょうから、
一概に、どれがどうというのは難しいかと思います。
状況に応じて選べるように引き出しを多くしておきたいといったところでしょうか。
勿論状況によって使えたり使えなかったりということもありますから
結局は知識よりも経験（都度検証を経た知識等）が重くなるのですが。
（因みに 電子掲示板での回答に適した記述 もあると思いますが私は苦手です（汗）
◆◆◆
（＃一応、回答）
test03を元に手を加えると、こんな書き方もあります。
◆◆◆
Sub testC()
　　Const S_REF_COLHEAD = "A2,C2,G2,H2,K2"' ←列の変更はここで指定

　　Dim rng As Areas ' 対象セルのある列全体をAreas指定。
　　Dim r As Range ' ループ用
　　Dim n As Long ' カウンタ
　　Dim i As Long ' ループ用

　　' 対象セルのある列全体それぞれをAreasで捉えてAreaごとに処理。
　　For Each r In Range(S_REF_COLHEAD).EntireColumn.Areas
　　　　For i = 2 To 10
　　　　　　n = n + 1
　　　　　　' r でポイントされた Area の中の i 番目のセルに値セット
　　　　　　r(i).Value = n
　　　　Next i
　　Next r
End Sub
◆◆◆
以下は、オマケ（野心的なコード）です。
（変数 i を引数にした処理がある場合は使い難い記述ですが）
あまり見かけない方法ですが色々能率的でシンプルに書けるので
私は好んでよく使ってた方法です。
◆◆◆
Sub testJ()' 7058428okg
　　Const S_REF = "(A:A,C:C,G:G,H:H,K:K) 2:10"

　　Dim r As Range ' ループ用
　　Dim n As Long ' カウンタ

　　' 対象セルすべてを順列を含めて指定してループ。
　　For Each r In Range(S_REF)
　　　　n = n + 1
　　　　r.Value = n
　　Next r
End Sub
◆◆◆
例えば、Ｍ列を追加するなら「,M:M」を加えて
　　Const S_REF = "(A:A,C:C,G:G,H:H,K:K,M:M) 2:10"
など、十分な説明をコメントに残した方がよいでしょうかね。
◆◆◆
サンプルとして行数固定で書きましたがコード内で可変にするには
　　Const S_REF = "(A:A,C:C,G:G,H:H,K:K) 2:"
としておいて
　　... Range(S_REF & 10000)
等のように文字列の連結で対応します。
◆◆◆
Excel:
「参照演算子」
Excel VBA:
「Rangeの引数としての参照文字列」
「For Each ... Next の処理順」
など、ちょっとお浚いして貰えれば、難しいことはないと思います。
◆◆◆
「参照演算子」の空白（スペース）は、
ふたつのセル範囲に共通するセル範囲を返します。
"(A:A,C:C,G:G,H:H,K:K) 2:10"は、
「（Ａ、Ｃ、Ｇ、Ｈ、Ｋ列） 且つ ２から１０行」という意味で、
"A2:A10,C2:C10,G2:H10,K2:K10"と同じ範囲を参照します。
以下、参考。
http://www.excel-jiten.net/formula/reference_operator.html
http://officetanaka.net/excel/function/tips/tips30.htm
単セル参照を列参照に直すのが大変なら
Debug.? Range("A2,C2,G2,H2,K2").EntireColumn.Address(False, False)
のようなものをを実行してイミディエイトウィンドウからコピー。
◆◆◆
「Rangeの引数としての参照文字列」はExcelのセル参照に倣います。
（違いは絶対参照を指示する"$"が無効になること位）
参照文字列に指定できるのは255文字までです。（４０列なら問題ないかと）
◆◆◆
「For Each ... Next の処理順」については実験で。
f0:
Range("A2:A10,C2:C10,G2:H10,K2:K10").Select
↑を＊実行後、ExcelウィンドウにてTABキーを長押し（or連打）＊（←以下略＊＊）
すると、アクティブセルが順番に遷移するのが見えると思います。
「For Each ... Next の処理順」を示しています。
でも、連続した範囲をG2:H10と表しているので、これでは、
望んだ順番に遷移しません。
t0:
Range("A2:A10,C2:C10,G2:G10,H2:H10,K2:K10").Select
１列毎に分けた記述に直して＊＊すると
課題のナンバリングの順番にアクティブセルが遷移します。
t1:
Range("(A:A,C:C,G:G,H:H,K:K) 2:10").Select
参照演算子の空白を使って＊＊しても
やはり望んだ順番に遷移します。
f1:
Range("(A:A,C:C,G:H,K:K) 2:10").Select
連続した範囲をG:Hと表しているので、これはNGです。
◆◆◆
備考として、test03の
col.Offset(i - 2).Value = n
は
col.Cells(i - 1).Value = n
や
col(i - 1, 1).Value = n
のような書き方もあります。
処理速度で僅かな改善がある筈ですが、今時のＰＣでは
差は少ないでしょうから自分で解り易い記述がいいでしょうね。
◆◆◆
上に紹介した以外に処理の速さ（軽さ）に特化した方法としては、
Areasごとに二段階配列変数の値配列をセットするのが速いかなぁと。
コメント書くのが大変なので提示は遠慮します。
◆◆◆
それではまた

お礼

くわしい回答、ありがとうございます。
そしてSub testCは始めて見た書き方ですが、すごいですね。
ずいぶん早くなりました。
オマケの解説もとても勉強になりました。
Range("(A:A,C:C,G:G,H:H,K:K) 2:10").Select　なんてことができるとは初めて知りました。
（半角スペースという参照演算子も知りませんでしたので）

これからもいろいろ教えてくださいね。
よろしくお願いします。

回答No.2

merlionXX

> 実際には列は約40列（固定）、行は1～2万行（変動）程度

ためしに列数はご提示の5列のままで2万行で実験してみました。
以下はApplication.ScreenUpdating = False　で画面更新を止めて測っています。

test01～03　それぞれ
42.23438 秒
44.29688 秒
39.375 秒

列数が40に増えたら差はもっとひらくはずです。

というわけで、TEST03がいいのでは？

お礼

実行速度からのアドバイスありがとうございます。
わたしもTimer関数を使って今試してみました。
それぞれ
40.078
42.90601
39.53101
でした。
やはりTest03のようですね。

回答No.1

hallo-2007

人それぞれと思いますが、私の場合は
Sub test03()＝＞Sub test02()＝＞Sub test01()
の順番でしょうか。
Sub test03()
運用で、列が変更された場合、コードを変更することなく、
誰でもがセルの値で変更することで使える親切設計

Sub test02()
他の人がコードを見た場合、昔のコードを見直した場合
（自分が作成したコードでも時間が経過すると忘れがち）
にわかりやすい。

Sub test01()
列番号が数式などで、数値としてしか得られない場合に仕方なく使う

こんなところでしょうか。

お礼

さっそくありがとうございます。
メンテナンスの観点からのご指摘、感謝いたします。