こんにちは
今回エクセルでセル選択範囲を調べる方法について探していたのですが”Selection.Address”が
見つかりました。
これは、結果を文字型で返す仕様となっていて、文字型では、応用処理がややこしそうになります。
離れた範囲で選択した場合"例 R1C1:R2:C2,R4C4:R5C5" のように返ってきます"
そこで、これを数値に変換したり、選択範囲が離れてていた場合は離れた範囲ごとに配列として返
してくれる関数やメソッドのようなものは、VBAに用意されていないでしょうか?
または、直接 希望する型で返ってくればそれでよいのですが、見つけることが出来ませんでした。
なお、エクセルのバージョンは2003です。
もしなければ作成する予定です。
A 回答 (5件)
- 最新から表示
- 回答順に表示
No.5
- 回答日時:
こんにちは。
#1~4、cjです。フラグ配列を使った例をあげることにしました。
応用できる機会が比較的多いと思います。
また、この方法だと、元々の行位置の順に格納できますから、
ソートする必要がありません。
また、重複の処理も簡単です。
一応、これは、元々の質問に対する答えに近いようにも思います。
Range オブジェクトのプロパティからフラグ配列を作って、
テーブル全体の値を格納した配列から
フラグ配列のインデックスに対応したレコードを
バッファに流し込みます。
(レコードの先頭が1行目から始まらない場合を考慮して書いていますので、その分少し長いです。)
(一般的なエラー処理も加えました。)
' ' ============================================================
Sub Re7761791c5()
Dim buf() ' 抽出レコードを流し込む配列
Dim mtxV() ' テーブル全体の値を格納する配列
Dim Target As Range ' Selectionを元に、テーブルからはみ出た範囲を削いだセル範囲
Dim a As Range ' Area ループ用
Dim nRowOffset As Long ' 『テーブル上の行番地』と『配列インデックス』との相対補正
Dim nYSize As Long ' テーブルの行サイズ
Dim nXSize As Long ' テーブルの列サイズ
Dim nTop As Long ' Area 先頭行の相対番地
Dim nBottom As Long ' Area 最下行の相対番地
Dim arFlg() As Boolean ' 抽出レコードをマークするフラグ配列
Dim cnt As Long ' 抽出レコード数カウンタ
Dim i As Long, j As Long ' ループ用カウンタ
' Range("G13:G14,B2:B3,E10:G13,C6:D7,F7:G7").Select ' ダミー
' ' セル範囲以外を選択中なら、抜ける
If TypeName(Selection) <> "Range" Then Exit Sub
With Sheets("リスト")
nRowOffset = 0 ' 先頭.Row - 1
' ' 最下行位置を取得して、行サイズを求める
nYSize = .Cells(Rows.Count, "A").End(xlUp).Row - nRowOffset
nXSize = 11 ' 列サイズ
' ' テーブル全体を捉える
With .Cells.Resize(nYSize, nXSize).Offset(nRowOffset)
' ' Selection について、テーブルから、はみ出た部分を削いでおく
Set Target = Application.Intersect(.Cells, Selection)
' ' Selection がテーブル内にない場合、抜ける
If Target Is Nothing Then Exit Sub
' ' テーブル全体の値を配列として格納しておく
mtxV() = .Value
End With
End With
' ' フラグ配列を行サイズで定義
ReDim arFlg(1 To nYSize) As Boolean
' ' Target 各Areaをループ
For Each a In Target.Areas
' ' Area の先頭行位置を相対位置で求める
nTop = a.Row - nRowOffset
' ' Area の行数を取得して、最下行位置を相対位置で求める
nBottom = nTop + a.Rows.Count - 1
' ' Area の先頭行から最下行に対応したフラグをTrueに設定する
For i = nTop To nBottom
' ' (他のAreaと重複している場合)既にTrueに設定してある場合は何もしない
If Not arFlg(i) Then
arFlg(i) = True
' ' 抽出するレコード数をカウント
cnt = cnt + 1
End If
Next i
Next a
' ' 抽出レコードを流し込む配列をレコード数と列サイズで定義
ReDim buf(1 To cnt, 1 To nXSize)
cnt = 0
' ' フラグ配列を総なめして、Trueならbufに流し込む
For i = 1 To nYSize
If arFlg(i) Then
cnt = cnt + 1
For j = 1 To nXSize
buf(cnt, j) = mtxV(i, j)
Next j
End If
Next i
' Range("M1").Resize(cnt, nXSize).Value = buf ' ダミー
' ' オブジェクト変数の解放
Set Target = Nothing
' ' 配列変数の初期化
' Erase mtxV, arFlg, buf
End Sub
' ' ============================================================
応用できる場面が見つかるといいのですけれど。
以上です。
No.4
- 回答日時:
こんにちは。
#1、2、3、cjです。#3補足欄へのレスです。ご自分で研究しながら着実に進んでいることが、こちらにも伝わってきます。
非常にいい印象ですし、こちらもやりがいがあります。
さておき、
複数のセルブロックを指定して、
最終的に必要なレコードだけを、
ひとつの2次元配列に、(できればSortして)格納したい
という【テーマ】に限って考えてみます。
まずは、#3でご提示のコードについて。
Select して Selection を追いかけるのは何かと紛れの元になりますから、
Range型の変数に一旦格納してみます。
Set Target = Application.Intersect(Sheets("リスト").Range("A:k"), Selection.EntireRow)
If Target Is Nothing Then Exit Sub
' ↑(Dim Target As Range)
これ↑で、
(テーブルの外を選択していたり、他のシートで実行してたり)エラーを回避できる意味もありますし、
後に続く処理が Target に対するものであることが明示的になり、より確実なものになります。
オブジェクト型の変数はプロシージャの最後で
Set Target = Nothing
のように解放してあげるようにしましょう。
'配列サイズ設定
の
> ReDim buf(i, 11)
正しくは
ReDim buf(1 To i, 1 To 11)
ですね。
配列変数を扱う時は、最小の添え字(LBound)が 1 なのか 0 なのか
(或いはそれ以外なのか)恒に、注意を払うようにしましょう。
ご提示のコードでは、buf(0, any)、buf(any, 0)がすべてEmpty値になっていて
不必要なことをしていることになります。
もう一点、.Intersectメソッドを使う利点として重複は既に除かれていることを利用すると、
i = Application.Intersect(Sheets("リスト").Range("A:A"), Target).Count
ReDim buf(1 To i, 1 To 11)
という纏め方も可能ではあります。
(簡単に書けるという意味です。処理が速くなる訳ではありません)
(同じ.Intersectメソッドを使うのにも、こちらは、
エラーになる要因が既にクリアになっているので省略して書けます)
'選択範囲を配列buf(行,列)へ設定
は、差し当たってダメ出しするほどの問題ではないのですけれど、
一貫性を通した書き方をすると、こんな感じでしょうか。
i = 0
For Each a In Target.Areas
For Each rw In a.Rows
i = i + 1
j = 0
For Each r In rw.Cells
j = j + 1
buf(i, j) = r.Value
Next r
Next rw
Next a
' ↑(Dim r As Range)
> For Each rw In a.Rows < これは#3で私が書いたことの実践なのでしょうけれど、
レコード毎に切り分ける必要がないのならば .Rows プロパティを使う必要もなくて、
今回の【テーマ】では .Areas プロパティすら省略できて、うんと簡単に
i = 1
j = 1
For Each r In Target
buf(i, j) = r.Value
If j = 11 Then
j = 1
i = i + 1
Else
j = j + 1
End If
Next r
' ↑(Dim r As Range)
↑こんな風に纏めることも可能です。
一旦、整理します。今回の【テーマ】では .Areas プロパティ無しでも出来るということで。
Sub Re7761791r3()
Dim buf()
Dim Target As Range, r As Range
Dim i As Long, j As Long
' Range("G13:G14,B2:B3,E10:G13,C6:D7,F7:G7").Select ' ダミー
Set Target = Application.Intersect(Sheets("リスト").Range("A:k"), Selection.EntireRow)
If Target Is Nothing Then Exit Sub
'配列サイズ設定
i = Application.Intersect(Sheets("リスト").Range("A:A"), Target).Count
ReDim buf(1 To i, 1 To 11)
'選択範囲を配列buf(行,列)へ設定
i = 1
j = 1
For Each r In Target
buf(i, j) = r.Value
If j = 11 Then
j = 1
i = i + 1
Else
j = j + 1
End If
Next r
Set Target = Nothing
End Sub
.Intersectメソッドを使う利点を最大限活かす手法にも触れておきます。
.Intersectメソッドで得られるTargetのすべて.Areasが、共通の列幅をもっているならば、
そのまま.Copyして貼りつけてしまうことで、
・重複は削除され・連結した配列として・Sortされた配列を
シート上に作成できます。
Sub Re7761791_40()
Dim buf()
Dim Target As Range
' Range("G13:G14,B2:B3,E10:G13,C6:D7,F7:G7").Select ' ダミー
Set Target = Application.Intersect(Sheets("リスト").Range("A:K"), Selection.EntireRow)
If Target Is Nothing Then Exit Sub
Target.Copy Destination:=Range("M2")
'選択範囲を配列bufへ格納
buf = Range("M1").CurrentRegion.Value
Set Target = Nothing
End Sub
作業セル(作業シート)を(一時的にでも)設けることが許されるならば、
上記のような手法も場合によって(目的によって)は有効です。
案外、配列変数を使わなくとも、目的が果たせるケースも少なからずあることでしょう。
(上例の "M2" は、上の行が空行、左の列が空列、以下空セル、という条件で適宜指定してください)
///
私、今、余裕がないので、このレスは一旦、ここまでにしてあらためて続きを書きます。
次に、今回の【テーマ】に即した形で、Sortも併せ、配列への格納方法を探ってみます。
外部オブジェクトは使わずに純粋にVBAだけを用いるレベルで、
なるべく難しい関数などは使わない形で書いてみようと思います。
ある程度処理の速度を速める意図で、
配列変数を使うメリット(VBA独特のメリット)を出せるように考えてみます。
ポイントはRangeオブジェクトへのアクセスを最小限にして、
メモリ上の配列変数での処理を優先させること、になります。
ただ、色々な方法があるので、どんなものを紹介したらいいか迷っています。
文字数制限に掛かってしまうので、どのみち、もう1レスすることにはなりますが、
ちょっと体力的な問題を抱えてたりもするので、少し、時間をくださいませ。
それでは、また。
No.3
- 回答日時:
こんにちは。
#1、2、cjです。#2補足欄へのレスです。
> リスト形式のデーターテーブル(シート)がある。
> ユーザーは処理対象としたいレコード(セル)を任意に選択する(複数可)。
> プログラムは、選択されたレコード(セル)を対象に処理(他のシートにコピーや印刷
> など。差込印刷)をする。
> これらのことをするのにプログラムはユーザーの選択セル情報を知る必要があるのでそ
> の方法を探していたところでした。
そういう目的ならば、行番地の数値を追いかけずとも、
Rangeオブジェクトで取得した方が何かと便利です。
次の例では、例えば、ユーザー操作で重複して選択されたセル範囲があった場合でも
自動的に重複を除いてくれます。
例えばコピーなら、そのままコピペ(VBAでも手作業でも)すれば
有用なレコードだけを整列して貼り付けできますし、、、。
割とお得な近道だと思いますが、どうでしょうか。
Sub Re7761791ja()
Range("B2,D4,B4:B5,B7:B9,B8,B11:B14").Select ' ダミー
MsgBox "Prep."
Application.Intersect(ActiveCell.CurrentRegion.EntireColumn, Selection.EntireRow).Select
MsgBox "Got" & vbLf & Selection.Address(0, 0)
End Sub
もしレコードごとのデータをレコードごとに切り分けて処理する必要がある場合は
For Each a In Selection.Areas
For Each rw In a.Rows
のようにも切り分け可能ですし、
配列に格納したデータを行番地を参照して取得するような方法にも応用できます。
また、レコードそのものではなく、キーとなるIDなどをポイントする目的でも
基本的には同じように処理するのが簡単です。
> いずれにしろ構想の段階ですが、文字型では、処理が難しくなるのではないのかなぁと
> 思い、数値型でのアドレス情報での処理をしようと思った次第です。
確かに、クエリやコマンドや正規表現が苦手という方は沢山いらっしゃいますから、
苦手意識がある人にはrange.Address でさえも難しいのかも知れませんね。
一般論としては文字列で処理するよりは数値で処理する方が優る、という場面が多いのも事実で
仰ることはよく解ります。
ただ、数値配列の取得も、数値配列を参照してRange オブジェクトを取得する方法も
Excelには用意されてませんので、その代わりになる機能を他に求めるのが普通のやり方だと思います。
そういう意味では#1で紹介した手法もどこかで役に立つことがあればいいですけどね。
注).Select、Selectionは概念提示の為便宜的に使っているもので実践には使いません。
一応。質問者さんがここでメソッドと呼んでいるのはプロパティですので確認してみてください。
とりあえず、以上です。
この回答への補足
cj_moverさん こんにちは
たびたびありがとうございます。
ご回答をいただいてからそれをヒントに何とか自分で解決しようとして、さらに調べたり
テストプログラムを走らせたりしてたら報告が遅くなってしまいました。
出来上がったコードが以下の通りです。
Dim buf(), a, rw
Dim i As Integer, j As Integer
Application.Intersect(Sheets("リスト").Range("A:k"), _
Selection.EntireRow).Select
'配列サイズ設定
i = 0
For Each a In Selection.Areas
i = i + a.Rows.Count
Next
ReDim buf(i, 11)
'選択範囲を配列buf(行,列)へ設定
i = 0
For Each a In Selection.Areas
For Each rw In a.Rows
i = i + 1
For j = 1 To 11
buf(i, j) = Cells(rw.Row, j)
Next
Next
Next
これの結果は、配列buf(行 ,列)にセルのデータが代入されます。
選択範囲が何個もあるときなど、配列の行側の順番が上から順にならないことがあります。
この辺は、改善したかったのですが、難しくなりそうなので諦めました。
この配列データを元に自分がしたい処理が出来ると思います。
ただ、このコードは、いまのスキルで書いた為、多少無駄があるのかもしれません。
関数等を利用して数行で出来る方法があれば、ヒントを頂けたら有り難く思います。
(実は、これを探していて時間がかかりました。 元々ないから見つからなかったのかも
しれません。)
No.2
- 回答日時:
なんか、ちょっとわかった気がしたので重ねてレスしてみます。
Range("A1:C5,D6:E10,G3:G12,B15:E16")
を例えにすると
matrix = [{1,1,5,3;6,4,10,5;3,7,12,7;15,2,16,5}]
で、得られるような
{top,left,bottom,right}
矩形範囲毎に絶対座標を配列にしたものを
さらに二次元配列や二段階配列にする、みたいなことでしょうかね?
そういうことでしたら
> そこで、これを数値に変換したり、選択範囲が離れてていた場合は離れた範囲ごとに配列として返
> してくれる関数やメソッドのようなものは、VBAに用意されていないでしょうか?
残念ながら用意されていないです。なので、ご自分で工夫するしかないですね。
少し気になるのは最終的にそれらの座標を元に何をしたいか、ということです。
そこまでの見通しがあれば、また違うアドバイスもできるかも知れませんけれど。
"...でなければ出来ない"事例、というのは
私の経験では、相当に特殊で、例えば、
Excel2003までは 8192Areas より多い範囲を返してくれない.SpecialCells メソッドを拡張して自作した時
ぐらいで、通常は
.Address プロパティ、.Row プロパティ .Column プロパティ、
Application.ConvertFormula メソッド、などを組み合わせ、
文字列処理して、セル範囲の参照文字列で工夫した方が
まだ、扱い易いように思います。
(私の想像力が乏しいだけで、必然的なニーズがあるのかも知れませんが。)
それから、
> もしなければ作成する予定です。
ということなので、一応、説明しておきますが、
Excel2003 で選択できるセルブロック(=矩形範囲、Area)の数は8192 までです。
ところが、.Address プロパティは 255文字までしか返しません。
255文字を超えた部分についてはバッサリ無視して、
不完全な結果だということを知ることさえ出来ません。
この点を理解した上で取り組まれた方がよいかと思います。
確実にやるなら、
.Address を採るにしても、Areaごとにするとか、
あらかじめ、Areas数を限定しておくとか、
対策が必要です。
なので、Areasごとに、.Row .Column を採っていった方が
(#1下から2番目の例)結局能率的な場合もあると思います。
あとは、二次元配列なのか、ユーザー定義型(Type)を配列にするか、とか、
何にせよ、目的次第ですけれども。
とりあえず、以上です。
この回答への補足
cj_moverさん こんにちは
アドバイスありがとうございます。
御回答の通りで、このような配列で返ってくることを求めていました。
次のような場合に使用出来ることを考えています。
エクセルでよく利用する使い方のではと自分自身で勝手に思っているのですが。
リスト形式のデーターテーブル(シート)がある。
ユーザーは処理対象としたいレコード(セル)を任意に選択する(複数可)。
プログラムは、選択されたレコード(セル)を対象に処理(他のシートにコピーや印刷
など。差込印刷)をする。
これらのことをするのにプログラムはユーザーの選択セル情報を知る必要があるのでそ
の方法を探していたところでした。
いずれにしろ構想の段階ですが、文字型では、処理が難しくなるのではないのかなぁと
思い、数値型でのアドレス情報での処理をしようと思った次第です。
あと、.Areasは今まで知らなかったメゾッドですが、この処理に利用できるのではない
のかなぁと感じました。
データ数の制限は、元データがそんなに大きくないのでとりあえずは大丈夫です。
No.1
- 回答日時:
こんにちは。
> そこで、これを数値に変換したり、選択範囲が離れてていた場合は離れた範囲ごとに配列として返
> してくれる関数やメソッドのようなものは、VBAに用意されていないでしょうか?
> または、直接 希望する型で返ってくればそれでよいのですが、見つけることが出来ませんでした。
ご希望、に対する私の理解が不十分なようなので、思い当たる限りの解釈で、
応用し易そうな手法だけ、列挙したコードを掲げます。
VBEで、Ctrl+Gキーを押すとアクティブになる[イミディエイトウィンドウ]
に表示される結果と、それに対応したコードとを照らし合わせれば、
大体のことは理解してもらえるように書いたつもりです。
Sub Re7761791a()
Dim matrix, arr, v
Dim rArea As Range
Dim r As Range
Range("A1:C5,D6:E10,G3:G12,B15:E16").Select ' ←テスト用のダミーです
If TypeName(Selection) <> "Range" Then Exit Sub
Debug.? "◆いわゆる普通のA1形式で .Address()を取得" _
& vbLf & " ? Selection.Address(0, 0)" _
& vbLf & " ⇒ " & Selection.Address(0, 0) & vbLf
Debug.? "◆R1C1形式で .Address()を取得" _
& vbLf & " ? Selection.Address(ReferenceStyle:=xlR1C1)" _
& vbLf & " ⇒ " & Selection.Address(ReferenceStyle:=xlR1C1) & vbLf
Debug.? "◆各Area毎 .Address()をA1形式で取得"
For Each rArea In Selection.Areas
Debug.? , rArea.Address(0, 0)
Next rArea
Debug.?
Debug.? "◆.Address()を"",""区切りで配列変数に格納したものを出力"
arr = Split(Selection.Address(0, 0), ",")
For Each v In arr
Debug.? , v
Next v
Debug.?
Debug.? "◆各Area毎 左上の .Row .Column 右下の .Row .Column"
For Each rArea In Selection.Areas
With rArea
Debug.? .Row, .Column;
With .Cells(.Count)
Debug.? , .Row, .Column
End With
End With
Next rArea
Debug.?
Debug.? "◆各Areaの値を配列変数に格納して配列のサイズを確認"
For Each rArea In Selection.Areas
Debug.? , rArea.Address(0, 0)
matrix = rArea.Value
Debug.? , "1 to "; UBound(matrix, 1), "1 to "; UBound(matrix, 2)
Next rArea
Debug.?
End Sub
cj_moverさん こんにちは
質問をしたものです。
早速ありがとうございます。
確認いたしますのですぐに返事が出来ませんが、一報としてお礼申し上げます。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Excel(エクセル) エクセルVBA 複数行にまたがっている選択を判定するには 2 2023/05/21 21:54
- Visual Basic(VBA) データのある範囲を選択するVBAについて 2 2022/09/03 00:20
- Excel(エクセル) エクセル 関数参照範囲のセルの表示 1 2023/03/16 14:55
- PDF PDFの表がエクセルに貼り付けられなくなってしまいました。 5 2022/06/03 09:07
- Excel(エクセル) Excelのプルダウンメニューの内容を人によって可変する方法 2 2023/03/28 14:52
- Visual Basic(VBA) シート間で同じ値があったら指定範囲をコピーして貼り付け 1 2022/11/07 08:01
- Excel(エクセル) エクセルVBA 作業後に選択範囲を解除する方法 5 2023/02/17 07:13
- Excel(エクセル) 【VBA】エクセルで選択した範囲の値のみをクリップボードにコピーするコードについて 3 2023/03/08 17:41
- Excel(エクセル) マクロ セルの選択 5 2022/08/12 22:47
- Excel(エクセル) VBA 特定の列に入っているテキストをコピペ 2 2023/06/14 11:24
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
array関数で格納した配列の型を...
-
ExcelのINDEXとMATCH関数でスピ...
-
Excelのセルの色指定をVBAから...
-
配列がとびとびである場合の書き方
-
表にフィルターをかけ、絞った...
-
[エクセル]連続する指定範囲か...
-
DataSetから、DataTableを取得...
-
エクセルで、絶対値の平均を算...
-
スプレットシートのGetTextにつ...
-
【VBA】ユーザーフォーム リス...
-
Datatableへの代入
-
Excel VBA 配列の分割について
-
.NET - 配列変数を省略可能の引...
-
仮想リストコントロールの表示
-
[VBA]改行入りのセルの値を配列...
-
エクセルVBAで変数をセルに一行...
-
【VBA】配列に数式を仕込む方法...
-
配列のSession格納、及び取得方...
-
VBAでの100万行以上のデータの...
-
VBAで指定期間の範囲を抽出し、...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
Excelのセルの色指定をVBAから...
-
array関数で格納した配列の型を...
-
ExcelのINDEXとMATCH関数でスピ...
-
[エクセル]連続する指定範囲か...
-
エクセルで、絶対値の平均を算...
-
VBA listBoxについて
-
配列がとびとびである場合の書き方
-
表にフィルターをかけ、絞った...
-
VBA 配列に格納した値の平均の...
-
[VBA]改行入りのセルの値を配列...
-
エクセルでエラーを無視して一...
-
iniファイルのキーと値を取得す...
-
二次元配列の中の各行の要素を...
-
SUMPRODUCT関数を用いた最小値
-
【VBA】ユーザーフォーム リス...
-
Split関数でLong配列に格納する...
-
【VBA】配列に数式を仕込む方法...
-
.NET - 配列変数を省略可能の引...
-
スプレットシートのGetTextにつ...
-
Excelのオートフィルタで抽出し...
おすすめ情報