エクセルで特定の列の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.1
- 回答日時:
人それぞれと思いますが、私の場合は
Sub test03()=>Sub test02()=>Sub test01()
の順番でしょうか。
Sub test03()
運用で、列が変更された場合、コードを変更することなく、
誰でもがセルの値で変更することで使える親切設計
Sub test02()
他の人がコードを見た場合、昔のコードを見直した場合
(自分が作成したコードでも時間が経過すると忘れがち)
にわかりやすい。
Sub test01()
列番号が数式などで、数値としてしか得られない場合に仕方なく使う
こんなところでしょうか。
No.2
- 回答日時:
> 実際には列は約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.3
- 回答日時:
お邪魔します。
個人的には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: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"は、
「(A、C、G、H、K列) 且つ 2から10行」という意味で、
"A2:A10,C2:C10,G2:H10,K2:K10"と同じ範囲を参照します。
以下、参考。
http://www.excel-jiten.net/formula/reference_ope …
http://officetanaka.net/excel/function/tips/tips …
単セル参照を列参照に直すのが大変なら
Debug.? Range("A2,C2,G2,H2,K2").EntireColumn.Address(False, False)
のようなものをを実行してイミディエイトウィンドウからコピー。
◆◆◆
「Rangeの引数としての参照文字列」はExcelのセル参照に倣います。
(違いは絶対参照を指示する"$"が無効になること位)
参照文字列に指定できるのは255文字までです。(40列なら問題ないかと)
◆◆◆
「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
1列毎に分けた記述に直して**すると
課題のナンバリングの順番にアクティブセルが遷移します。
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
のような書き方もあります。
処理速度で僅かな改善がある筈ですが、今時のPCでは
差は少ないでしょうから自分で解り易い記述がいいでしょうね。
◆◆◆
上に紹介した以外に処理の速さ(軽さ)に特化した方法としては、
Areasごとに二段階配列変数の値配列をセットするのが速いかなぁと。
コメント書くのが大変なので提示は遠慮します。
◆◆◆
それではまた
くわしい回答、ありがとうございます。
そしてSub testCは始めて見た書き方ですが、すごいですね。
ずいぶん早くなりました。
オマケの解説もとても勉強になりました。
Range("(A:A,C:C,G:G,H:H,K:K) 2:10").Select なんてことができるとは初めて知りました。
(半角スペースという参照演算子も知りませんでしたので)
これからもいろいろ教えてくださいね。
よろしくお願いします。
No.5
- 回答日時:
こんにちは
一応参考として配列版を掲げておきます(コメント抜きです)。
仕様が定まりませんからカスタマイズし易い書き方で、内容的には叩き台レベルです。
testC と testJ の手法を組合せたものに加えてVBAならでは配列の使いこなしがポイントです。
基本技術の組合わせだけで特に変わったことはしていませんが、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.6ベストアンサー
- 回答日時:
こんにちは、
レスをありがとうございます。
◆◆◆
>arrMatrix(i) = mtxTemplate
>これって、一次元配列の要素に二次元配列をいれているのでしょうか?
はい、その通りです。
サイズ(行数と列数)を定義済みの空の二次元配列(mtxTemplate)を
一次元配列(arrMatrix)の要素に代入しています。
◆◆◆
あれこれ書き過ぎたみたいなので、少し整理しますが、
配列を使うかどうかは二の次三の次でして、
(1)処理対象のセル範囲(過不足なく)全体を先に取得しておく事
(2)処理対象のセル範囲を Areas で捉え、Area 毎に処理する事
の2点が私のレスのメインテーマです。
(ただひとつ testJ だけは↑(2)と無関係ですが)
処理の選択肢が増え、効率的になること、を示したかったということです。
その為の方法として参照文字列や参照演算子を見直してみてはどうか
という提案も書きました。
◆◆◆
>実際には...作業はもっと複雑です。
無理に汎用性を持たせようとして却って、
解り難い書き方をしてしまった 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))
' ' 二次元配列のサイズ(先頭行から末尾行*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をご指導いただき感謝感激です。
これからもよろしくお願い申し上げます。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Visual Basic(VBA) 前回ご教授いただいたコードに覚えたてのループ処理で品名りんごAから順に20回for nextでループ 7 2023/01/13 22:01
- Excel(エクセル) B列に文字がはいったらA列に数字が入るマクロードを完成させたい 4 2023/04/21 01:58
- Visual Basic(VBA) いつもお世話になっております、VBAで教えて頂きたいのですが 2 2022/05/05 22:20
- Visual Basic(VBA) ExcelVBAに関する質問 3 2023/02/17 10:47
- Excel(エクセル) VBAの指示の内容 昨日こちらでご教示頂いたのですが初心者な為、一つ一つの指示が何をやっているのかわ 2 2022/10/25 18:08
- Visual Basic(VBA) 稀に1円合いません? Sheet1から金額と個数を貼り付ける下記コードで、金額を切り上げるコードを何 3 2022/09/05 15:11
- Visual Basic(VBA) 数字が「0」の列を削除するため、下記のコードを実行しましたが、コンパイルエラーSubまたはFunct 3 2022/12/04 00:00
- Visual Basic(VBA) 別シートから年齢別の件数をカウントしたいの続き 5 2023/01/24 00:16
- Visual Basic(VBA) excel vbaでvlooupの変数がわかりません。 7 2022/05/30 09:35
- Visual Basic(VBA) VBA処理追加 こちらでご教示頂いたのですが回答完了させてしまいましたのでこちらからまた質問させてく 2 2022/10/27 09:57
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
【Excel関数】UNIQUE関数で"0"...
-
エクセルで特定の文字列が入っ...
-
エクセル マクロ オートフィ...
-
セルの色によって条件文をつけ...
-
エクセル 上下で列幅を変えるには
-
アクティブになっている行をマ...
-
電話番号の入力方式が違うデー...
-
AのセルとB行を比較して、一致...
-
VBAで色の付いているセルの行削除
-
[EXCEL]ボタン押す→時刻が表に...
-
エクセルVBAのEntireRow.Hidden...
-
Excel グラフのプロットからデ...
-
エクセル マクロで数値が変っ...
-
EXCELで最後の行を固定
-
エクセルVBA:データ端に画...
-
エクセルマクロで特定の文字を...
-
excelのデータで色つき行の抽出...
-
Excelでカタカナ・ひらがな・英...
-
結合されたセルをプルダウンの...
-
特定の文字がある行以外を削除...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
エクセルで特定の文字列が入っ...
-
エクセル マクロ オートフィ...
-
【Excel関数】UNIQUE関数で"0"...
-
[EXCEL]ボタン押す→時刻が表に...
-
結合されたセルをプルダウンの...
-
エクセル マクロで数値が変っ...
-
Excel グラフのプロットからデ...
-
AのセルとB行を比較して、一致...
-
エクセル 上下で列幅を変えるには
-
Excel ウインドウ枠の固定をす...
-
特定の文字がある行以外を削除...
-
excelのデータで色つき行の抽出...
-
エクセル2016で時間を入力して...
-
excel 小さすぎて見えないセル...
-
EXCELで最後の行を固定
-
エクセルVBA 最終行を選んで並...
-
VBAで色の付いているセルの行削除
-
エクセルマクロで偶数行(又は...
-
エクセルのセルに指定画像(.jpg...
-
罫線の斜線を自動で引くマクロ
おすすめ情報