行全体・列全体の指定 – ExcelVBAの散策とruby

2016/03/21

参考サイト：　

• Excel VBA 入門講座 行の指定
• Excel VBA 入門講座 列の指定

《目次》

• 1.　概要
• 2.　マクロを登録するスクリプト
• 3.　win32oleのスクリプト
• 4.　exlapのスクリプト
• 5.　補足

1.　概要

　行全体を指定するには EntireRowプロパティを、列全体を指定するには EntireColumnプロパティを用います。

　たとえば下のように記述します。

  Range("A3").EntireRow.Value = "test"
  Cells(3,1).EntireRow.Value = "test"

　上の２行は同じ処理をするものです。３行目全体を「test」という文字で埋め尽くします。

　Excel2003のxlsファイルなら、３行目の第１列から第256列まで、どのセルにも「test」と書き込まれます。

　xlsxファイルであれば、１列から16,384列までが「test」になります。

  Range("D1").EntireColumn.Value = "test"
  Cells(1,4).EntireColumn.Value = "test"

　上の２行も同じ処理を行います。４列目について、第１行から第65,536行まで「test」が書き込まれます。xlsxファイルなら 1,048,576行までです。

　EntireRowもEntireColumnも、書き込みで用いることはあまりないと思います。

　実線でこれらを利用することが多いのは、行全体・列全体の削除といったことでしょうか。

　Cells(5,1).EntireRow.Delete とすれば、５行目全体が削除されます。

　Cells(1,3).EntireColumn.Delete とすれば、３列目全体が削除されます。

　この Delete は、Clear と異なり、行全体または列全体が跡形もなくなくなります。

　Clearの方は、セルの中に書かれている内容が消去されるだけで、行全体・列全体がなくなる訳ではありません。

　Deleteで第２行目全体を削除すると、それまで第３行目だったものが繰り上がって第２行目になります。

　Deleteで第４列目全体を削除すると、それまで第５列目だったものが左にずれて第４列目になります。

　複数の行について行全体を指定したり、複数の列について列全体を指定することもできます。

　Range("A2:A4").EntireRow.Value = "test" と書けば、第２行目から第４行目の全体を「test」で埋め尽くします。

　Range("B1:E1").EntireColumn.Value = "test" であれば、第２列目から第５列目の全体を「test」にします。

目次に戻る

2.　マクロを登録するスクリプト

　「概要」で述べてきた事柄をVBAマクロとして登録するためのスクリプトを掲げます。

　これまでと同様に、マクロには Control + j というショートカットキーを割り当てます。

 # encoding: Windows-31J
 require "./exlap"
 
 macro_str = <<'EOS'
 Sub Macro1()
     Range("A1:E7").EntireRow.Value = "test"
     Range("A8:A9").EntireRow.Value = "8行・9行"
     Cells(3,1).EntireRow.Clear
     Range("C1").EntireColumn.Clear
    Cells(7,1).EntireRow.Delete
End Sub
EOS

macro_name = "Macro1"
File.delete("Book1.xls")  if test(?e, "Book1.xls")  # 既存のBook1.xlsを削除
Exlap.new("Book1.xls") do |wb|
  wb.macro_add(macro_str)
  wb.Application.MacroOptions("Macro"=>macro_name, "ShortcutKey"=>"j")
  wb.save
end

　上のマクロでは、７行×５列全体に「test」を書き込み、8行目と9行目全体には「8行・9行」を書き込みます。

　そのあと、第３行目と第３列目のセルの中身を消去(Clear)しています。

　そして、最後に７行目を削除(Delete)しています。

目次に戻る

3.　win32oleのスクリプト

　前述のマクロと同じ処理を行うwin32oleのスクリプトは下のようになります。

 # encoding: Windows-31J
 require "win32ole"
 
 def fullpath(filename)
     fso = WIN32OLE.new("Scripting.FileSystemObject")
     return fso.GetAbsolutePathName(filename)
 end
 
 Dir.glob("./Book1.xls*") {|fname|  File.delete(fname)}
app = WIN32OLE.new("Excel.Application")
wb = app.Workbooks.Add()
ws = wb.Worksheets("Sheet1")
ws.Range("A1:E7").EntireRow.Value = "test"
ws.Range("A8:A9").EntireRow.Value = "8行・9行"
ws.Cells(3,1).EntireRow.Clear
ws.Range("C1").EntireColumn.Clear
ws.Cells(7,1).EntireRow.Delete
wb.SaveAs(fullpath("Book1"), (app.Version.to_f >= 12.0) ? 51 : -4143)
app.Quit

　VBAマクロの部分は、ほぼそのまま取り入れています。特別の変更・工夫はしていません。

　今回、ユーザーがExcel2007以降を使っている場合は xlsxファイルを保存するようになっています。

　行全体、列全体といった時に、Excel2003までとそれ以降では最大の行数と最大の列数が異なるため、Excel2007以降を起動している場合に、xls形式で保存しようとすると警告メッセージが出てしまいます。

　SaveAs() の書き方がやや面倒なものになっていますが、要は、Excel2003までならxls、Excel2007以降ならxlsxで保存するという意味です。

目次に戻る

4.　exlapのスクリプト

　同じ処理を行うexlapのスクリプトを掲げます。

　今回の主題である EntireRow, EntireColumn の記述部分は、win32oleのスクリプトと同じです。

　xls と xlsx の違いは、exlapでは気にしなくて大丈夫です。

 # encoding: Windows-31J
 require "./exlap"
 
 File.delete("Book1.xls")  if test(?e, "Book1.xls")  # 既存のBook1.xlsを削除
 Exlap.new("Book1.xls") do |wb|
   ws = wb.ss("Sheet1")
   ws.Range("A1:E7").EntireRow.Value = "test"
   ws.Range("A8:A9").EntireRow.Value = "8行・9行"
   ws.Cells(3,1).EntireRow.Clear
  ws.Range("C1").EntireColumn.Clear
  ws.Cells(7,1).EntireRow.Delete
  wb.save
end

目次に戻る

5.　補足

　ワークシートオブジェクトには Rows, Columns というプロパティもあります。これらを使って行全体，列全体を扱うこともできますが、「あれも・これも」と書いても仕方ないので触れません。

　ただ、Rows.Count によって最大行数、Columns.Count によって最大列数を取得できることを記しておきたいと思います。

　VBAマクロで記述すると下のようになります。

Sub Macro1()
    MsgBox "最大行数：" & ActiveSheet.Rows.Count & _
        "　　最大列数：" & ActiveSheet.Columns.Count
End Sub