[[20110718113132]]　『複数シートの最終行数を表示』（sala）　ページの最後に飛ぶ

『複数シートの最終行数を表示』（sala）

マクロ初心者です。マクロのご伝授をお願いします。

ひとつのブックの複数シートの目次を作る下記のまくろの”概要”の列に
シートごとのA列の最終行（件数）を表示させたいのですが、
どのように変更したらいいのでしょうか？

よろしくお願いします！

Sub sheetname()

  Dim objSheet As Object
  Dim intLoop As Integer

  If MsgBox("シート名一覧を作成しますか？", vbYesNo) = vbNo Then Exit Sub

  intLoop = ActiveCell.Row

  For Each objSheet In ActiveWorkbook.Sheets
    ActiveWorkbook.ActiveSheet.Cells(intLoop, ActiveCell.Column).Value = objSheet.Name
    intLoop = intLoop + 1
  Next

End Sub

[エクセルのバージョン]2007
[OSのバージョン]Windows Vista

 ＞”概要”の列
 ってのは、どこにあるのでしょう？

 一つ右隣りなら、
    ActiveWorkbook.ActiveSheet.Cells(intLoop, ActiveCell.Column + 1).Value = "=COUNTA(" & objSheet.Name & "!A:A)"
 これで、隣のセルに =COUNTA(Sheet1!A:A) と言った式が入りますが。

 End(xlUp)　で調べるなら
 MsgBox objSheet.Range("A" & Rows.Count).End(xlUp).Row
 こんな感じで。

 (HANA)

HANAさま、すみません！　

 ”概要”があるマクロは
 下記でした・・・・大変、大変失礼しました（；＿；）

 Option Explicit

 Sub 目次挿入()
 Const INDEX_SHEET_NAME As String = "目次" 'シート名設定

 Dim IndexSheet As Worksheet
 Dim ws As Worksheet
 Dim i As Integer

     '同じ名前にシートが無いかチェック
     For Each ws In Worksheets
        If ws.Name = INDEX_SHEET_NAME Then
            ws.Select
            MsgBox "同じ名前のシートがあります。" + vbCr + "同名のシートを変更、又は削除し てから再実行してください。"
            Exit Sub
        End If
     Next

     'ワークシートを先頭に挿入
     Worksheets.Add Before:=Worksheets(1)

     Worksheets(1).Name = INDEX_SHEET_NAME

     Set IndexSheet = Worksheets(INDEX_SHEET_NAME)

     '目次シートのセルにシート名挿入
     For Each ws In Worksheets
        i = i + 1
        If i = 1 Then
            Cells(2, 2).Value = "シート名"
            Cells(2, 4).Value = "概要"
        Else
            Cells(i * 2, 2).Value = ws.Name
            'シート名にリンク設定
            ws.Hyperlinks.Add Anchor:=Cells(i * 2, 2), Address:="", SubAddress:=ws.Name & "!A1"
        End If
     Next

 End Sub

 マクロ超初心者のため、インターネットで探して貼り付けて使っています。
 　
 上のマクロを実行すると、シート名の列の2つ右に
 ”概要”という列が現れるようになっていて、この列に、各シートごとのA列の最終行（件数）を表示したいというお願いでした。

 本当に、すみません。

 どうか、どうか、よろしくお願いします。
 sala

 いくつか確認させて下さい。

 B4セルに最初のシート名が表示されると思いますが
 そのシート名が Sheet1 だった場合
 D4セルに =COUNTA(Sheet1!A:A)
 の式を入れて返される値は、希望する値と一致しますか？

 数式を入れておけば、データ量が変わった時に連動して変わるので
 良いのではないかと思いますが。。。

 単純にCOUNTA関数で取得出来る様なもので無かったり
 数式が入っていると都合が悪かったり
 データ量が変わる事は無いから値で入力しておきたい
 等の場合は、他の方法に成ります。

 どちらが良いのでしょう？
 まずは検討してみて下さい。

 それから、目次シートに見出しを入れるのが
 　Cells(2, 2).Value = "シート名"
 　Cells(2, 4).Value = "概要"
 の部分ですよね？

 実際のシート名を入れるのが
 　Cells(i * 2, 2).Value = ws.Name
 の部分です。
 「シート名」と言う見出しを入れたのと同じ列にシート名が入っていくので
 二つのコードで、変わっている所は
 　Cells(2     , 2).Value = "シート名"
 　Cells(i * 2 , 2).Value = ws.Name
         ~~~~~~この部分。

 「概要」と言う見出しを入れたのと同じ列にもシート名を入れようと思えば
 　Cells(2, 4).Value = "概要"
 から、同じ様に変更して
 　Cells(i * 2 , 4).Value = ws.Name
 ですから、後は　最終行をどのように取得するかだけが問題になってくると思います。

 (HANA)

HANAさま、早速ありがとうございます。

 データ量が変わった時に連動して変わる・・・様に、お願いします。
 "概要"の列に、最終行（データ件数）を表示できれば・・・
 下の表のようになればいいなと思っています。

 　　A　　　B　　　　　 C　 　　D　　　　E
  1 
  2 　 　シート名　　　   　　概要　
  3 　
  4 　　 産地別データ     　 1,023    
  5 
  6 　　 製造元別　　　　　 　115
  7 
  8 　　　機種別　　　　　　158,860
  9 
 10 　 　不具合別　　　　  　　26

 わがままなお願いで、すみません。(sala)

 えっと、それで　色々やってみてもらえましたか？

 処理を想像しながら、コードを読んでみてください。

 COUNTA関数を埋め込むので良いなら、最初のコメントの右辺
 "=COUNTA(" & objSheet.Name & "!A:A)"
 を、該当のセルに書き込むことに成ります。

 ただし、シート名が最初のコードでは「objSheet.Name」だったのが
 次のコードでは「ws.Name」に成っているので、変更して下さい。

 これを書き込むセルは、次のコメントに書きましたが
 Cells(i * 2 , 4)　のセルです。

 目次挿入のコードの
 ＞ '目次シートのセルにシート名挿入
 のコメント以降を重点的に読んでみて下さい。

 (HANA)

HANAさん、できました！

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

 ただ1点だけ・・・
 ちゃんと『概要』の列に件数は出たのですが、
 なぜか、全ての『シート名』の後ろに、”!A1”が付いて、
 　（例：『産地別データ!A1』『製造元別!A1』・・・）
 となっていたので、
 勝手に、最後のあたりのリンク設定コードの　& "!A"を　& ""　に変えたら
 出なくなりました。

 それでいいのでしょうか？

HANAさま

 上のでいくと、ハイパーリンク設定ができませんでした。
 どのような変更で、シート名から『!A1』が消えるのでしょうか？
 よろしくお願いします！

 件数の表示は出来た様で良かったです。

 シート名の方は。。。
 最初はちゃんと表示されていたのに、件数表示の変更をしてから
 うまく表示されなくなったのですか？

 でしたら、そのコードを載せてください。

 それとも、最初からシート名の後ろに 「A1」が付いていたのですか？
 こちらで試した限りではつかないですが。。。

 (HANA)

（=＾▽＾）HANAさま、できました!

 Cells(i * 2, 4).Value = "=COUNTA(" & ws.Name & "!A:A)"　の前の
 Cells(i * 2, 2).Value = ws.Name を消してしまっていました。

 ご心配を　おかけして、失礼いたしました。
 HANAさまのわかりやすいアドバイスのおかげで、大変勉強になり、感激です。
 会社での作業がどれだけ短縮されるか計り知れません。
 明日の仕事が楽しみです。
 (sala)

 ご自身で問題箇所が見つかりましたか。
 良かったです。

 せっかくなので、
 ＞Cells(2, 4).Value = "概要"
 この部分も適切なタイトルに変えた方が良いかもですね。

 近いコードを見つけられるのですから
 少しコードが読めると
 出来ることがもっと多く成ると思います。

 ちなみに、コードはインデントをつけると読みやすく成ります。
 たとえば
     For Each ws In Worksheets
     If i = 1 Then
     Cells(2, 2).Value = "シート名"
     Else
     Cells(i * 2, 2).Value = ws.Name
     End If
     Next
 インデントの無いものより
 インデントのあるものの方が
     For Each ws In Worksheets
        If i = 1 Then
            Cells(2, 2).Value = "シート名"
        Else
            Cells(i * 2, 2).Value = ws.Name
        End If
     Next
 何処から何処までが一つのまとまりなのか
 分かりやすく成ります。

 ↑で載せておられる　目次挿入コードを修正して
 インデントをつけてみました。
 確認してみていただけると良いと思います。

 (HANA)

HANAさま、再びアドバイスを、ありがとうございます！

 （＾□＾）あ、ほんとうにそうですね。
 ”概要”ではなくて, ”件数”にした方がいいですね。

 『一行づつイメージしながら』
 『インデントをつけて』
 なるほど、なるほど～！　インデントをつけると、わかりやすくてイメージしやすくなりますね。
 HANAさまにお世話になって、大きな勉強になりました。
 大切に保存して、これからの参考にさせていただきたいと思います。

 別段問題無い事なのですが

 ＞Set IndexSheet = Worksheets(INDEX_SHEET_NAME)
 ここで、IndexSheet に目次シートを Set していますが
 それ以降では使われていない様です。

 この一行と、変数の宣言の所
 ＞Dim IndexSheet As Worksheet
 は、無くて良さそうです。

 以下、別件で・・・せっかくですので、マクロの記録も試してみて下さい。

 新しいブックを用意して、
 　Sheet1のA1セルで右クリック→ハイパーリンク(H)
 　表示文字列に「AAA」　セル参照は、Sheet2のA1セル
 をマクロの記録にとります。

 ↓ライブラリ「マクロの自動記録」 
https://www.excel.studio-kazu.jp/lib/e4b/e4b.html

 すると、A1セルには
 　AAA　と表示され
   Sheet2 の A1 セルに
 ハイパーリンクが設定されます。

 また、こんなコードが出来ると思います。
Sub Macro1()
    ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
        "Sheet2!A1", TextToDisplay:="AAA"
End Sub

 記録した動作と、結果から　コードを推測すると。
 ActiveSheet.Hyperlinks.Add　・・・・ハイパーリンク設定のおまじない？
 Anchor:=Selection ・・・・・・・・・選択していたセル(A1セル）に設定されたので
 　　　　　　　　　　　　　　　　　　ここには、設定したいセルを指定するのかな？
 SubAddress:= "Sheet2!A1"　・・・・・Sheet2のA1セルにリンクする様にしたよね！！
 TextToDisplay:="AAA"　・・・・・・・セルに「AAA」って表示されてるし。

 見つけてこられたコードを見ると
 ws.Hyperlinks.Add Anchor:=Cells(i * 2, 2), Address:="", SubAddress:=ws.Name & "!A1"
 　Anchor:=Cells(i * 2, 2)　のセルにハイパーリンクが設定されていて
 　SubAddress:=ws.Name & "!A1" のセルにリンクされる様に成ってる。

 それなら
    ActiveSheet.Hyperlinks.Add Anchor:=Cells(i * 2, 2), Address:="", SubAddress:= _
        ws.Name & "!A1", TextToDisplay:=ws.Name
 ってしたら、その上で　シート名をセルに入れる
 Cells(i * 2, 2).Value = ws.Name
 のコードが不要かも？

 まぁ、変な所で改行されているので　程良い所で改行。
            ActiveSheet.Hyperlinks.Add Anchor:=Cells(i * 2, 2), Address:="", _
                SubAddress:=ws.Name & "!A1", TextToDisplay:=ws.Name

 (HANA)

こんばんは　HANAさま。

 今日も　素敵なアドバイスを、ありがとうございます☆

 HANAさま方式で、マクロをひとつずつ紐解いて勉強をしていくと、頭と心に染み込みますね！
 とってもわかりやすい説明に感動です。

 ハイパーリンク設定の呪文も、目からウロコです　（！θ！）
 な　　　る　　　　ほ　　　　　ど　！

 そして、早速やってみました、マクロの記録を。
 今までのコードから、2行消し、ハイパーリンクのコードを変えて　1行消し、
 変更しても、変わらず　ちゃんと実行されました。
 感謝感激です。

 HANAさまのパソコン教室が近くにあれば、私は間違いなく毎日 通いつめるに違いありません。

 『一行づつイメージしながら』
 『インデントをつけて』に加えて、
 『いらないコードが何となくわかるように』も 合わせて目指します。
 今後とも、よろしくお願いいたします。
(sala)

 そう言っていただけると、レス屋冥利に尽きますね。
 ありがとうございます。

 いくら書いてもきりがないので、一旦終息方向へ。。。

 これから色々な事をやっていこうと思った時に
 マクロの記録で出来たコードは、大いに役に立つと思います。
 上手に活用して下さい。

 また、コードの「Hyperlinks」の単語内にカーソルを持って行って
 [F1]キーを押すと、その単語に関連するヘルプが開きます。
 　　「Anchor」では『キーワードが見つかりません』と出ますが
 　　「TextToDisplay」だと、また開きます。
 ヘルプの日本語は分かりにくいですが、こまめに確認する様にしておくと
 ある日突然ピースがはまったりします。
 　　これは、ワークシートの方のヘルプも一緒ですね。

 これからエクセルと仲良しになって貰えると思い
 楽しみにしています。＾＾

 (HANA)

コメント返信：

[ 一覧(最新更新順) ]

[[20110718113132]] 『複数シートの最終行数を表示』（sala） ページの最後に飛ぶ

[[20110718113132]]　『複数シートの最終行数を表示』（sala）　ページの最後に飛ぶ