エクセルのセルの長さが可変のデータに、それぞれ一番上のデータの名前を定

質問

エクセルのセルの長さが可変のデータに、それぞれ一番上のデータの名前を定義するにはどうしたらいいんでしょうか？

先日ここの質問で解決し

↓こういうデータを作成することができました。

　　　Ａ列　　　Ｂ列　　　Ｃ列　　　Ｄ列　　　
１　ＭＭ銀行　ＮＮ銀行　ＬＬ銀行　ＯＯ銀行
２　ＰＰ支店　ＳＳ支店　ＵＵ支店　ＶＶ支店
３　ＱＱ支店　ＴＴ支店　　　　　　　　ＷＷ本店
４　ＲＲ支店　　　　　　　　　　　　　　ＸＸ支店
５　　　　　　　　　　　　　　　　　　　　ＹＹ支店

このデータを使用して入力規則のリストを使用したいので、名前の定義を使用し、
「Ａ１」から「Ｄ５」を範囲選択し、まとめて「上端」を名前として設定する処理を行いました。
そうするとＡ列とＢ列とＣ列の何も入っていないところにも名前が設定されていると思うんですが、
リストを設定したところの選択肢にスペースのセルもすべて表示されてしまいます。

１列１列名前を付けていけばいいのですが、何十列もあるため処理に時間がかかってしまいます。

それぞれの銀行の支店の数も違いますし、銀行数自体増える可能性があるので
それぞれの列の行数も増減しますし、列のデータも増減します。

ひとつの列の最後の行を見に行くマクロはわかるのですが、
列の最後も同時に見に行くとなると、考えてみてもわかりませんでした。
マクロは人が書いたものを、調べていって理解できる程度です。

今までもこのサイトの過去のものを見て勉強させていただきました。
今回もインターネットで調べまわったのですが、よくわかりませんでした。
よければ、どうしたらいいのか教えていただきたいと思っています。

よろしくお願いします。

回答No.5

hige_082 · ベストアンサー

＞一番上の行の値を名前の定義で2行目～ｎ行目につけたいんです。
変更してみましたので、試してみてください

Sub Macro1()
Dim i
For i = 1 To Cells(1, Columns.Count).End(xlToLeft).Column
ActiveWorkbook.Names.Add Name:=Cells(1, i).Value, _
RefersTo:="=" & Range(Cells(2, i), Cells(Rows.Count, i).End(xlUp)).Address
Next i
End Sub

お礼

お礼がおくれてしまい申し訳ありません＞＜

教えていただいたマクロをしようした所、
思ったとおりに動きました！

本当に助かりました！　ありがとうございました！

回答No.4

mitarashi

#1です。修正漏れがありました。汗顔の至りです。
同じデータ構造のシートをコピーしてテストしたので、元のシートを参照しているのに気付きませんでした。
なお、データを後から手で追加しても、そのまま機能する様に、コードで動的な名前をつけるのをやってみたかったものです。
Sub test()
Dim myRange As Range
Dim i As Long

Set myRange = Range(Cells(1, 1), Cells(1, ActiveSheet.Columns.Count).End(xlToLeft))
For i = 1 To myRange.Cells.Count
ActiveWorkbook.Names.Add Name:=myRange.Cells(i).Value, RefersToR1C1:= _
"=OFFSET(" & ActiveSheet.Name & "!R1C" & i & ",1,0,COUNTA(" & ActiveSheet.Name & "!C" & i & ")-1,1)"
Next i
End Sub

お礼

ちょっとドバタバとしていて、返事がおくれてしまいすみません！

3回も答えて頂いて、本当にありがとうございます。

職場のエクセルは2007なのですが、教えていただいたとおりのマクロを登録すると
質問したとおり、名前を定義することができました！

しかしそのデータを使って、入力規則でリストをしようとするとエラーが
でてしまい。自分でもなんでかわからなくて、せっかくたくさん考えていただいたのに
申し訳ないです。

でも本当にありがとうございました！

回答No.3

hige_082

処理を行うシートを表示してから
実行してください

Sub Macro1()
Dim i
For i = 1 To Cells(1, Columns.Count).End(xlToLeft).Column
ActiveWorkbook.Names.Add Name:=Cells(1, i).Value, _
RefersTo:="=" & Range(Cells(1, i), Cells(Rows.Count, i).End(xlUp)).Address
Next i
End Sub

参考まで

補足

この質問のシステムの使い方がまだわからないので、「補足入力」の使い方間違っていたらごめんなさい＞＜

前回に引き続き、回答ありがとうございます！

また教えていただいたマクロを登録し実行したところ
列と行の名前の定義はとてもうまくいったんですが、
一番上の行（銀行名の行）には名前を定義したくないときには
マクロのどこを変えたらできますかね＞＜
一番上の行の値を名前の定義で2行目～ｎ行目につけたいんです。

回答のマクロを登録して
「入力規則」で範囲に「=○○銀行」と設定したときに、支店だけじゃなく銀行名まで
でてきてしまいました。

質問の仕方がうまくできなくて、理解しにくくてすみません。
もしよければ、また教えていただきたいです。
お願いします！

回答No.2

mitarashi

#1です。Sheet1でしか動かないコードになっておりましたので、修正しました。失礼いたしました。
Sub test()
Dim myRange As Range
Dim i As Long

Set myRange = Range(Cells(1, 1), Cells(1, ActiveSheet.Columns.Count).End(xlToLeft))
For i = 1 To myRange.Cells.Count
ActiveWorkbook.Names.Add Name:=myRange.Cells(i).Value, RefersToR1C1:= _
"=OFFSET(" & ActiveSheet.Name & "!R1C" & i & ",1,0,COUNTA(Sheet1!C" & i & ")-1,1)"
Next i
End Sub
'おまけで動作確認用です。
Sub test2()
Dim myRange As Range
Dim i As Long

Set myRange = Range(Cells(1, 1), Cells(1, ActiveSheet.Columns.Count).End(xlToLeft))
For i = 1 To myRange.Cells.Count
Application.Goto Reference:=myRange.Cells(i).Value
MsgBox "次の範囲を選択"
Next i
End Sub

お礼

ありがとうございます！

回答No.1

mitarashi

こんなものでいかがでしょうか。ただし、xl2000用のコードですので、他バージョンで動かなかったらご容赦ください。（リスト用データの列内に他のデータが存在していない事を前提としております）
Sub test()
Dim myRange As Range
Dim i As Long

Set myRange = Range(Cells(1, 1), Cells(1, ActiveSheet.Columns.Count).End(xlToLeft))
For i = 1 To myRange.Cells.Count
ActiveWorkbook.Names.Add Name:=myRange.Cells(i).Value, RefersToR1C1:= _
"=OFFSET(Sheet1!R1C" & i & ",1,0,COUNTA(Sheet1!C" & i & ")-1,1)"
Next i
End Sub

お礼

ありがとうございます！