[[20151013154144]] 『指定した範囲のセルが全て空の場合に行を削除した』(PNC) >>BOT

[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]

 

『指定した範囲のセルが全て空の場合に行を削除したい』(PNC)

PNCと申します。

指定した範囲のセルが全て空(Null)の場合に行を削除したいです。
やりたいこととしては、以下になります。

・やりたいこと
変数で取得した範囲が全て空(Null)だった場合、その行を削除する。

・データ情報
1行づつ商品の情報が管理されており、縦がメーカー、横に製品名、表内に入荷日があります。
表内のセルにはデータが入っているものと入っていないもの(入荷したものはデータがあり、未入荷のものはNull)がありますが、
指定された範囲のどこかに1つでもデータが入っている場合は、その行を残したいです。
逆に、指定した範囲が全て空(Null)だった場合、行ごと削除を実施したい。
※ 指定した範囲のメーカーの製品を1つでも入荷していたら行を残したい

同じような情報が複数シートに分かれていて、データ自体はF列から始まっているのですが、
シートによっては列の数が異なるため、範囲指定が難しく、自動で列の最終の値を取得し、ループで処理したいと考えております。

宜しくお願いします。

< 使用 Excel:Excel2010、使用 OS:Windows7 >


 よく分かってないのですが
 下記のような表だとして、B社の行を消したいということですか?

    |[E]     |[F]    |[G]    |[H]    |[I]                 
 [1]|メーカー|製品A  |製品B  |製品C  |                    
 [2]|A社     |10月1日|       |       |                    
 [3]|B社     |       |       |       |←この行を消したい?
 [4]|C社     |       |10月2日|10月3日|                    

 >シートによっては列の数が異なるため、範囲指定が難しく、自動で列の最終の値を取得し、ループで処理したいと考えております。 
 これが真だとすれば、範囲指定は製品名が並んでいる最後の列ですよね。
 1行目に必ず製品名が並んでいるなら、Cells(1,Columns.Count).End(xlToLeft)で取得できると思います。 
(稲葉) 2015/10/13(火) 17:30

稲葉さん

返信ありがとうございます。

ご指摘の通り、製品名が並んでいる最後の列を取得したいのですが、
縦の情報も取得した後、頂いた表で言いますとF2〜H4を対象範囲とし、
3行目のように、日付が何もない行を消したいと思っております。

1行目には必ず製品名が並んでおりますが、どこから何をしてつ繋ぎ
それを処理すればいいのかサッパリで…

申し訳ありませんが、もう少しご助言いただけると助かります。
宜しくお願いします。
(PNC) 2015/10/13(火) 17:49


 本当は一度自分で作ってほしいんだけど、パソコンたたむので・・・
 一応↑のデータでテストしてあります。
 何も聞かずに消すので、必ずデータのバックアップを取ってから実行してください。

    Option Explicit
    Sub pnc()
        Dim sh     As Worksheet
        Dim cnt    As Long
        Dim lastC  As Range
        Dim r      As Long
        Dim delRow As Range
        Dim chkRow As Range
        For Each sh In Sheets                                       'シートをループさせる
            Set lastC = sh.Cells(1, sh.Columns.Count).End(xlToLeft) 'シートの1行目の最終列を取得する
            For r = sh.Cells(sh.Rows.Count, "E").End(xlUp).Row To 2 Step -1      'E列(メーカー名)の最終行から2行目迄をループする
                Set chkRow = sh.Range(sh.Cells(r, lastC.Column), sh.Cells(r, "F"))  'ループ中の行のF列から最終列までを変数にセットする
                cnt = Application.WorksheetFunction.CountA(chkRow)               'CountA関数でデータの有無を調べる
                'データが無ければ、消す用変数に入れる。
                If cnt = 0 Then
                    If delRow Is Nothing Then
                        Set delRow = chkRow.EntireRow
                    Else
                        Set delRow = Union(delRow, chkRow.EntireRow)
                    End If
                End If
                cnt = 0
            Next r
            '消す変数のリセット
            If Not delRow Is Nothing Then
                delRow.Delete
            End If
            Set delRow = Nothing
        Next sh
    End Sub
 10/14 0845 一か所間違ってたので訂正。
(稲葉) 2015/10/13(火) 17:58

 横から失礼します。

 各シートのタイトル行は1行目で統一、開始列も、たとえば E列で統一ということであれば
 稲葉さんの回答で解決ですね。

 かりに、列についても各シート、開始列は、シートによってばらばらということであれば

 ・もし、各シート上に、この表しかない、表の外側に備考や日付や、とにかく表に関係のないものは
  一切存在しないということなら、領域は、シート.UsedRange で把握できますね。

 ・もし、表の外側に表とは関係のないものも存在するということであれば、たとえば1行目タイトル行の
  "メーカー"といった文字列を Find なり Match で検索して、その列を把握する方法もありますね。

 ついでに、細かなことなんですが。
 Null といえば、一般には【値がない】という意味ですので、セルがNullという表現も、あながち間違いではないですけど
 エクセルでNullといえば、【特殊な状態下の特殊な値】をさします。
 たとえば、複数セルの背景色を取得しようとしたときに、その領域の背景色がばらばらであれば
 背景色はこれだと返すことができないので、Null値がかえされます。
 この判定は、IsNull関数で行います。

 エクセルの世界で、空白は、一般には Empty で表現します。この判定は IsEmpty関数で行います。

(β) 2015/10/14(水) 09:52


>稲葉さん

回答ありがとうございます。
自分でソース作成できす申し訳ありません。
展開いただいたソースで試してみます。

>βさん

実は、列もシートによりバラバラなので、
アドバイスいただいたものをMIXして試してみます。

また、Excelの空白の扱いについて、ご丁寧にありがとうございます。
今後は Empty と表現させていただきます。

画像が貼れないのでうまく表現出来ませんが、
ソースを細かく分解した場合、どういう動作を行い
空白セルの範囲を指定するのか中々イメージがつきません。。。

例えば、メーカー列の中に1つでも空白があれば、
そこの上のセルをデータの最下とみなされる気がしますが、
メーカー列に空白があったりします。

データがところどころ入っていない場合にも
空白範囲を限定するやり方があればご教授いただきたく。

宜しくお願いします。

(PNC) 2015/10/14(水) 12:27


 全く分かりません。
 1)列がバラバラの意味は、メーカー名が記入されている列がばらばらということですか?
  その場合、βさんから御指摘いただいたとおり、メーカー名の項目に目安となる文言はありますか?
  
 2)シートがバラバラの意味は、整形しないシート名が含まれているということですか?
  整形を必要とするシート名、あるいはシート内に整形が必要と分かる言葉は含まれていますか?

 3)>例えば、メーカー列の中に1つでも空白があれば、 
  >そこの上のセルをデータの最下とみなされる気がしますが、 
  >メーカー列に空白があったりします。 
  手動操作で、メーカー名が記入された列の一番下の行を選択して、Ctrl+↑を押してください。
  一番最後に入力されているメーカー名迄行きつくはずです。
  これが sh.Cells(sh.Rows.Count, "E").End(xlUp) この動作になります。

 4)>データがところどころ入っていない場合にも 
  これは列単位で見たの話ですか? それとも行単位で見た話ですか?

 こちらは画像を添付されても参照できない可能性が高いので

http://www.excel.studio-kazu.jp/kw/20110209184943.html

 こちらを参考に、実際の表例をいくつかアップしてください。
(稲葉) 2015/10/14(水) 12:43

 稲葉さんから指摘の通り、具体的なシートイメージを2〜3つ、momoさんのユーティリティを使ってアップしてください。

 領域の規定方法は、数多くありますが、そのレイアウトに適した方法で取得することが必要です。

 こんな場合はこんなやりかたと、今思いつくものを羅列しても、わかりにくいだけだと思います。
 実際のレイアウトに沿って、その場合は、こういうやり方がいいですとアドバイス可能です。
 また、あわせて、このレイアウトでは、別のこの方式だとこんなように不具合がでますといった
 参考コメントも可能になるので、PNCさんにとって有益だと思います。

(β) 2015/10/14(水) 17:04


コメント返信:

[ 一覧(最新更新順) ]


YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki. Modified by kazu.