[[20170131080040]]　『A1を含めたUsedRange』（マリオ）　ページの最後に飛ぶ

『A1を含めたUsedRange』（マリオ）

 UsedRangeが、D5:H10だったときに、
 A1セルとUsedRangeを含めた領域（A1:H10）は、
 マクロで、どのように表現するのでしょうか？
 ど忘れしました。ご教授願います。

＜使用 Excel：Excel2003、使用 OS：Windows10 ＞

 Range("A1",ActiveSheet.UsedRange)  とか。（私は、もっぱら、この書き方）

 シート指定なら Sheets("hoge").Range("A1",Sheets("hoge").UsedRange)

（β） 2017/01/31(火) 08:15

 >βさん
http://www.niji.or.jp/home/toru/notes/8.html
 こちらのサイトに最終行、最終列の求め方があります。コレ↓
 *******************************************************************************
 <S6> 書式付きセルを除外する （UsedRange）
 With ActiveSheet.UsedRange
    MaxRow = .Find("*", , xlFormulas, , xlByRows, xlPrevious).Row
    MaxCol = .Find("*", , xlFormulas, , xlByColumns, xlPrevious).Column
 End With
 *******************************************************************************

 開始行、開始列を求めるためには、
 ★ActiveSheet.Range("A1", ActiveSheet.UsedRange)
 の記述の仕方が分からないと、と思いまして。
 ActiveSheet.Range("A1", UsedRange)では、ないのですね。
 ありがとうございます。

（マリオ） 2017/01/31(火) 08:34

 Sub test2()
    Dim sh As Worksheet
    Set sh = ThisWorkbook.Sheets("Data")
    MsgBox "test1 - " & sh.Range("A1", sh.UsedRange).Address
    MsgBox "test2 - " & sh.Range(sh.Range("A1"), sh.UsedRange).Address
    MsgBox "test3 - " & sh.Range(sh.Cells(1.1), sh.UsedRange).Address
 End Sub
（マリオ） 2017/01/31(火) 08:50

 もちろん　セルに　罫線　がつけられていたり　数式で　"" になっているところも、UsedRange に含まれますから
 そういったシートを相手にするなら、ケースバイケースで、適切な把握コードにしなければいけないのは
 明白ですね。

 また目的にもよるでしょうね。
 値はないけど罫線で表領域が確保されている。その最終セルまでを把握しなければいけない場合もあるでしょう。
 そうだった場合は、マリオさんがアップされた値があるセルのみを相手にすると、不具合がでますね。

（β） 2017/01/31(火) 08:52

 追加で、ご存じだとは思いますが     MsgBox "test3 - " & sh.Range(sh.Cells(1.1), sh.UsedRange).Address

 これは以下でもいいですよ。

 Sub こんな書き方も()
    Dim sh As Worksheet
    Set sh = ThisWorkbook.Sheets("Data")
    MsgBox Range(sh.Cells(1.1), sh.UsedRange).Address(External:=True)
 End Sub

（β） 2017/01/31(火) 08:57

 Findの「"*"」（第1引数）
 空でないなんらかの値が入っていればマッチしますだから、
 空白に見えても、「スペース文字」が入っている。
 また、βさんがおっしゃるように
 空白に見えても、「数式で =""」が入っている。
 は気を付けないといけませんね。

 セルのアドレスを取得する(Addressプロパティ)のことですかね。
https://www.moug.net/tech/exvba/0050094.html

 ■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
 '下記コード、A1セルに値が入っている場合だけ、正しく表示されない
 'ので、*******で囲まれている部分を追加しました。

  Option Explicit

  Dim myRng As Range

  On Error Resume Next ' ■■■■■
     Dim MaxRow As Integer, MaxCol As Integer, MinRow As Integer, MinCol As Integer
     With ActiveSheet.UsedRange
          MaxRow = .Find("*", , xlFormulas, , xlByRows, xlPrevious).Row
          MaxCol = .Find("*", , xlFormulas, , xlByColumns, xlPrevious).Column
     End With
     With ActiveSheet.Range("A1", ActiveSheet.UsedRange)
          MinRow = .Find("*", , xlFormulas, , xlByRows, xlNext).Row
          MinCol = .Find("*", , xlFormulas, , xlByColumns, xlNext).Column
     End With
     '****************************************
     If ActiveSheet.Range("A1") <> "" Then
        MinRow = 1: MinCol = 1
     End If
     '****************************************
     With ActiveSheet
          Set myRng = .Range(.Cells(MinRow, MinCol), .Cells(MaxRow, MaxCol))
     End With
  On Error GoTo 0 '■■■■■■■■
（マリオ） 2017/01/31(火) 09:14

 >値はないけど罫線で表領域が確保されている。その最終セルまでを把握しなければいけない場合もあるでしょう。
 そうか～、そんなことまで考えてませんでした。

（マリオ） 2017/01/31(火) 09:17

 >>セルのアドレスを取得する(Addressプロパティ)のことですかね。

 いえいえ、違います。

 sh.Range(なんたら) ではなく Range(なんたら) でもいいですよ　というつもりです。

 この場合、Application.Range(なんたら) あるいは Excel.Range(なんたら) と解釈されて
 その詳細は ( ) 内の　なんたら　に依存しますよ　と　ＶＢＡが扱ってくれます。

 じゃぁ、すべて　その書き方が楽じゃないかということですけど、

 Range("A1",別シート.なんとか) この場合、A1 はないてもわらっても、（標準モジュールの場合）アクティブシートになっちゃいますから
 1004エラーで叱られますね。
 なので、こういった場合は 別シート.Range("A1",別シート.なんとか) と記述せざるを得ないわけです。

（β） 2017/01/31(火) 09:21

何がしたいか解ってないですが。。。＾＾；

＞'下記コード、A1セルに値が入っている場合だけ、正しく表示されないので

Sub test()

    Dim myRng As Range
    Dim rngBottom As Range
    Dim rngRight As Range
    Dim rngTop As Range
    Dim rngLeft As Range

    With ActiveSheet
        Set myRng = .Range(.Range("A1"), .UsedRange)
    End With

    With myRng
        Set rngBottom = .Find("*", .Cells(1), xlValues, , xlByRows, xlPrevious)
        Set rngRight = .Find("*", .Cells(1), xlValues, , xlByColumns, xlPrevious)
        Set rngTop = .Find("*", .Cells(.Count), xlValues, , xlByRows, xlNext)
        Set rngLeft = .Find("*", .Cells(.Count), xlValues, , xlByColumns, xlNext)
    End With

    If Not rngBottom Is Nothing Then
        With Application
            Set myRng = .Range(.Range(rngTop, rngLeft), .Range(rngBottom, rngRight))
        End With
        myRng.Select
        MsgBox myRng.Address(False, False)
    Else
        MsgBox "エラー"
    End If
End Sub

Afterの位置を適正に指定すればA1に何かあっても特に問題が無いと思います。
（検索範囲内の最後のセルとか）

あと、On Error ～　を安易に使わないようにしましょう。
ましてや、コード全体にエラーを無視するように書くなんてもってのほか。
同じセル範囲を検索しているのだから、
1個見つかれば、他がNothingになることはない。
逆に1個Nothingなら全部Nothingになる。
それでエラー処理を出来ると思います。

あと、
Rangeプロパティの引数は
Range（左上の単体セル,右下の単体セル）
出なくても左下、右上でも、範囲で指定しても大丈夫っぽいので、
参考になれば。。。

（まっつわん） 2017/01/31(火) 12:58

 >まっつわん　さん
 （まっつわん） 2017/01/31(火) 12:58のｺｰﾄﾞは、
 ずばり、自分がやりたかったことです。

 >Afterの位置を適正に指定すればA1に何かあっても特に問題が無いと思います。 
 Findの第2引数で、検索開始セルを指定できるってことなんですね。
 「.Cells(1)」で、myRngセル範囲の左上、
 「.Cells(.Count)」で、myRngセル範囲の右下
 ↑こういうことでしょうか？

 さらに、第3引数で、「xlValues」としておけば、
 数式の「=""」で空白に見えるセルも拾いませんね。
 スペースが入っていて、空白に見えるセルは拾っちゃいますけどね。
 これは、Forループ使って、If LEN(Tirm(c.Value)))<>0 Thenで
 処理するしかないのかな～。ニーズがないか。

 If Not rngBottom Is Nothing Then
 ↑これなら、On Error ～いらないか。なるほど。
 シートが、まっさらな時に、エラー1004って出るけど何やろ、程度で（笑）

（マリオ） 2017/01/31(火) 14:00

 > 「.Cells(1)」で、myRngセル範囲の左上、
 > 「.Cells(.Count)」で、myRngセル範囲の右下
 > ↑こういうことでしょうか？

そういうことです。

実験マクロ

Sub test2()

    Dim Rng As Range
    Dim i As Long

    Set Rng = Range("C3:G6")

    For i = 1 To Rng.Count
        Rng.Cells(i).Select
        MsgBox Rng.Cells(i).Address(0, 0)
    Next
End Sub

セル範囲はセルの集合とみなします。
集合の各要素には内部的に番号が振られます。
実験の結果、横優先で番号が振られていることが解ります。
つまり、左上が1番で右下がセル範囲のセルの個数と同じ番号になります。
WorkSheets
WorkBooks
等も同じことです。
つまり一番右のワークシートは、
Worksheets(worksheets.Count)
と表せます。

＞スペースが入っていて、空白に見えるセルは拾っちゃいますけどね。
そういうセルが存在してはならないなら、置換機能でスペース文字をなくしちゃえばいいでしょう。
その辺は臨機応変に＾＾

＞シートが、まっさらな時に、エラー1004って出るけど何やろ、程度で（笑）
こういう事象は確認できませんが、
むしろ、
エラー1004
って出て欲しいです。
On Error Resume Next
として、エラーが出ないようになってると、
不具合に気づかなかったり、デバッグで迷子になったりします。
この辺は癖が出ちゃうので普段から気を付けた方がいいと思います。

（まっつわん） 2017/01/31(火) 15:27

コメント返信：

[ 一覧(最新更新順) ]

[[20170131080040]] 『A1を含めたUsedRange』（マリオ） ページの最後に飛ぶ

[[20170131080040]]　『A1を含めたUsedRange』（マリオ）　ページの最後に飛ぶ