飛び飛びに存在する空白列を削除するには?

Question 68.1 Previous Next
飛び飛びにある空白行を削除する方法に対して、マクロを使わず並び替えで出来るとの回答を読みました。
飛び飛びにある空白列を削除したいのですが(並び替えは使えないので・・・)よい方法はあるでしょうか。
Excel 2002を使っています。
Answer   Copyright (C) 2004.6.15 永井善王
トップページのアクセスカウンタが 200万を突破しましたので、その記念として、ご質問の受付を本日開始しましたところ、さっそくご応募いただきました。飛び飛びに存在する空白行を削除するには? をお読みいただいたとのこと、ありがとうございます。

どうして、「飛び飛びにある空白
」ができるのか、いささか疑問を感じ、そもそも、その列の全てのセルを空白にするときに、その列を削除しておけば済むことではないかとも思いますが、そのようになってしまったなら仕方ないので、削除する方法を考えてみましょう。

いろいろあるかとは思いますが、「並び替えは使えないので・・・」に挑戦してみます。(というよりもそれを参考になさったので、その方法で進めます) ただし、データ量があまりに多くてメモリが小さいと、動作が遅くなるかもしれません。
 第1図:処理前のシートの例 (Sheet1)            第3図:処理後のシート (Sheet3)
処理前のシート 処理後のシート

第1図が飛び飛びに空白列があるシートで、マクロの最初でA5セル(最後のデータ行の次の行)に、下記の数式をセットします。
  =IF(A1="","",COLUMN())
そして、A5セルをコピーして、B2:F5セル(データ列の末尾まで)へ貼り付けます。 お分かりかと思いますが、この数式の意味は、各列の1行目のセルが空白でなければ列番号をセットします。

 第2図:行列を入れ替えて貼り付けてソートした状態 (Sheet2)
処理前のシート 次に、準備が終わった第1図をコピーし、別のシートに行列を入れ替えて値を貼り付けます。

そして、並べ替えます。
このときの留意点は、先頭行がタイトル行ではなくてデータであることです。

次に、これをコピーして、さらに別のシートに貼り付けて、右端列を削除すると第3図のようにできあがります。

マクロを作成するなら、以上のストーリーで自動記録すれば、下記に似たマクロができるはずです。
Sub Macro1()
    Sheets("Sheet1").Select
        Range("A5").Select
        Selection.Formula = "=IF(A1="""","""",COLUMN())"
        Selection.AutoFill Destination:=Range("A5:F5"), Type:=xlFillDefault

        Range("A1:F5").Select
        Selection.Copy
    Sheets("Sheet2").Select
        Range("A1").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
            SkipBlanks:=False, Transpose:=True

        Selection.Sort Key1:=Range("E1"), Order1:=xlAscending, Header:=xlNo, _
            OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
            SortMethod:=xlPinYin, DataOption1:=xlSortNormal

        Selection.Copy
    Sheets("Sheet3").Select
        Range("A1").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
            SkipBlanks:=False, Transpose:=True
        Rows("5:5").Select
        Selection.Delete Shift:=xlUp
End Sub
作成したマクロが正常に動作することを確認してから、必要ならばマクロの一部を修正します。
修正カ所は、セル範囲、列番号などが固定されていますから、データ量の変動に対応できるようにします。 たとえば、数式をセットする直前に Sheet1の第n行までデータが入っているか調べて、A列n+1行セルに数式を設定します。

データが入っている行を調べる方法がわからない場合は
範囲を検出して選択 を参考にしてください。 また、セルなどを変数で指定する方法は 変数名で範囲指定して選択する が、さらに詳しい解説は 自動記録した範囲指定のコードを変数指定に修正するには? などがあります。

Excel VBA Macro