[[20160204173516]]　『VBAにて　可変列の最終行を取得したいのですが・＝x（さと）　ページの最後に飛ぶ

『VBAにて　可変列の最終行を取得したいのですが・・』（さと）

初歩的なことかもしれませんが、教えてください。

まず可変する列の最終列を取得し、
（Set f = Cells(6, Columns.Count).End(xlToLeft) '6行目に項目名があるため）

その最終列から2列左＋6行下に移動
（Set r1 = f.Offset(7, -2) 'そもそも2列左の列を選択したい＋6行下からレコードが存在するため）

この「r1」を最上段セルとし、
最下段セル「r2」（途中空白あり）をxlUpを用いて選択した上で
r1～r2を選択し、空白セルがある場合＝メッセージを出す
ということをしたいのですが、
r2を算出する時点でうまいコーディングができずにいます。

※変数は「f」「r1」「r2」を Rangeとしています。

良いアドバイスをいただけないでしょうか。
よろしくお願い致します。

※初心者故うまく説明ができておらず、過不足があるかもしれませんが、
　ご質問いただければ回答しますので、何卒お手柔らかにお願い致します。

＜使用 Excel：Excel2013、使用 OS：Windows7 ＞

 こんな感じで...

 Sub test()
    Dim r As Range, c As Range
    With Rows(6).Find("*", , , , 2, 2)
        Set r = Range(.Cells, Cells(Rows.Count, .Column)).Find("*", , , , 1, 2)(7, -1)
        MsgBox r.Address  '<- 2列左,6行下が取得されているか確認
    End With
    Set c = Range(r, Cells(Rows.Count, r.Column)).Find("*", , , , 2, 2)
    If c.Row <= r.Row Then MsgBox r.Address & " の下行にデータ無し": Exit Sub
    If Application.CountBlank(Range(r, c)) Then
        MsgBox "空白あり"
    Else
        MsgBox "空白無し"
    End If
End Sub
（seiya） 2016/02/04(木) 18:56

seiyaさん

早速のご教示、ありがとうございます。
一定の方法しか思いついていなかたので、とても参考になりました。

ただ、これですと最下段まで6行下にズレてしまようで
当然（枠外になるので）「空白」が存在してしまいます。

何か策は無いものでしょうか・・・。
（さと） 2016/02/05(金) 09:29

 どのようなデータ配列がされて、何を取得したいのかを詳しく例を挙げて説明してもらえますか？
（seiya） 2016/02/05(金) 09:38

seiyaさん

行・列の可変があるマトリクスの最終3列で
そのマトリクスの値が正しいかをチェックしています。
（今回必要としているのはIDチェックの列になります＝先頭列にもあるのですが、列が長くなった時用に左側にも入る仕組みになっています。）

ただ、何かエラーがあった時に　その左側のIDが空白になってしまうことがあるらしく、
それをチェックしたい！というのが主旨です。

そのID列が最終列から2つ左の6行目にフィールド名があり、
そこから7行下から実際のIDが入る！という仕組みです。

要するに
後ろから3列目の12行目～表の最下段に「空白が存在するか」をチェックしたい
というカンジです。

説明になっていますでしょうか？

そもそも12行目を開始とすれば良・・・かったりするんでしょうか？

（さと） 2016/02/05(金) 09:52

 >後ろから3列目の12行目～表の最下段に「空白が存在するか」をチェックしたい 
 この[最下段]とは、[後ろから3列目]の最後にデータのある行、という意味ですか？
（seiya） 2016/02/05(金) 09:57

seiyaさん

そうなります。
但し、途中に空白が存在する・・・かもしれない
というチェックをしたいのです。

ちなみに、（今表を確認していて気付いたのですが（今さらですみません））
最終列は（行としては同じ動きをしますが）空白が入らない仕様になっています。
（単に行内の修正（SUM）しているので、空白が途中にあっても「0」の表示になるからです。）
最終列の12行目～最下段を選択し、それを左に2列移す・・という方法はアリでしょうか？

（さと） 2016/02/05(金) 10:04

※↑「修正(SUM)」ではなく、「集計(SUM)」です。
誤字失礼しました。
（さと） 2016/02/05(金) 10:06

 少し整理しましょう。

 1) 列項目が6行目にある。
 2) 行・列共に可変で最終列には数式が入力され、各行の集計がされている。

 この状況で
 最終列から3列左の列に列項目(6行目)からその列のデータがある最終行に空白があるか調べる。
 もし1行でも空白がある場は最終列の数式？値？をその列に移す？

 ということですか？

（seiya） 2016/02/05(金) 10:19

seiyaさん

整理、ありがとうございます。
少し異なる部分があるので、そこを説明させていただきます。

1・2はそのままの認識で大丈夫です。

条件を追記すると・・
3.項目名は6行目だが、実レコードが入っているのはそこからまた7行下からになります。
（要するに、項目名と実レコードの間に空白が6行入ってしまうフォームになっているんです。）
4.実レコード（13行目～最下段）内に空白があるかをチェックし、メッセージを表示させる。
※12行目だと思っていましたが、6行目の6行下なので13行目でした。失礼しました。

単純に「項目名のある6行目～最終行をチェック」してしまうと
当然、空白があるため「空白アリ」になってしまうわけです。

最終列にも同じく 6行目に項目名があり、そこから6行は「空白」その下（要するに12行目）から最下段までは
空白は無く、実レコードが入っている。というカンジです。
※最終列の左隣列の実レコードは全て空欄になっています。

いかがでしょう？

【↓イメージ↓】　※ズレたらすみません！

行数

 1
 2
 3
 4
 5
 6　　　　　　　　・・・　　ID　　備考　　数量
 7
 8
 9
10
11　　No.　　ID　　
12
13　　1       a　　　　　　　a             8
14    2       b　　　　　　　              0
・    ・     ・             ・            ・
・    ・     ・             ・            ・
・    ・     ・             ・            ・
x     x       y　　　　　　　y             z

                            ↑この場合の「b」の抜け＝空白をチェックしたい　というのが主旨です！

（さと） 2016/02/05(金) 10:42

↑
書きながら混乱してしまったようで・・・すみません！

・6行目に項目がある
・そこから6行「空白」があり、その下の13行目から実レコードが入る
です。

大変失礼しました！！

（さと） 2016/02/05(金) 10:47

 表が....

 最終列から3列左の意味が最終列を含めたものなのか不明なので、以下で調整してみてください。

 Sub test()
    Dim LastR As Long, x
    ' 最終列から左3? 2?列目 少々曖昧...
    With Cells(6, Columns.Count).End(xlToLeft)(8, -1)  '<-  "-1" を調整して正確な列を指定。
        MsgBox .Address, , "最終列から3列目の13行目" '<- 取得できているか確認
        '最終列の最終行を取得
        LastR = Columns(.Column).Find("*", , , , 1, 2).Row
        MsgBox LastR, , "最終列の最終行" '<-確認
        x = Application.CountBlank(Range(.Cells, Cells(LastR, .Column)))
        If x > 0 Then
            MsgBox "空白あり"
        Else
            MsgBox "空白無し"
        End If
    End With
End Sub
（seiya） 2016/02/05(金) 11:19

seiyaさん

ありがとうございます！
＋わかりにくい事ですみませんでした。：最終列から2列左です。

頂いたコードそのままでうまくメッセージが出ました！！
何から何まで本当にありがとうございました！！！

（さと） 2016/02/05(金) 11:26

seiyaさん

ちなみに・・・
図々しいご相談ですが、
この空白セルに色を（例えば「赤：ColorIndex = 3」）など入れることも可能ですか？

→ご教示いただいたコードに少し手を加え、設定させてみたんですが
　どうもうまくいかなくて・・・・

本当にすみません！！（最初から言え！ですよね＞＜；）

（さと） 2016/02/05(金) 11:38

seiyaさん

追記ですが（今、確認して発覚したので後追いですみません！）
空白チェックするマトリクスのシートは、
「既存」で存在しないらしいです。
＝後から（同じシート名になりますが）作成しているらしいです。

シートの存在有無と存在する場合は「MATRIX」シートの最終列・・・・・という流れで
ご教示いただけませんでしょうか。

よろしくお願いします！！！！

（さと） 2016/02/05(金) 11:46

 これで検証してください。

 Sub test()
    Dim LastR As Long, x, i As Long
    With Sheets("MATRIX")
    ' 最終列から左3? 2?列目 少々曖昧...
        With .Cells(6, Columns.Count).End(xlToLeft)(8, -1)  '<-  "-1" を調整して正確な列を指定。
            'MsgBox .Address, , "最終列から3列目の13行目" '<- 取得できているか確認
            '最終列の最終行を取得
            LastR = .Parent.Columns(.Column).Find("*", , , , 1, 2).Row
            'MsgBox LastR, , "最終列の最終行" '<-確認
            With .Parent.Range(.Cells, .Parent.Cells(LastR, .Column))
                .Interior.ColorIndex = xlNone
                x = Application.CountBlank(.Cells)
                If x > 0 Then
                    For i = 1 To .Count
                        If .Cells(i).Value = "" Then .Cells(i).Interior.Color = vbRed
                    Next
                Else
                    MsgBox "空白無し"
                End If
            End With
        End With
    End With
End Sub
（seiya） 2016/02/05(金) 12:02

seiyaさん

ここまでご教示いただいたら、あとの微調整はやれそうです！
ありがとうございました！！

（さと） 2016/02/05(金) 12:53

コメント返信：

[ 一覧(最新更新順) ]

[[20160204173516]] 『VBAにて 可変列の最終行を取得したいのですが・＝x（さと） ページの最後に飛ぶ

[[20160204173516]]　『VBAにて　可変列の最終行を取得したいのですが・＝x（さと）　ページの最後に飛ぶ