Excel VBAの覚え書(SpecialCellsメソッド編)~可視セル(表示されているセル)の操作

2024-01-07

Excelで可視セル(表示されているセル)の操作を行う場合、RangeオブジェクトのSpecialCellsメソッドを使用します。

■SpecialCellsメソッドの詳細

RangeオブジェクトのSpecialCellsメソッドを実行すると、特定の条件を満たすセルの集まり(Rangeオブジェクト)を作成できます。

SpecialCellsメソッドの定義

SpecialCellsメソッドの定義は、下表の通りです。

SpecialCellsメソッド
説明 指定した型と値に一致する全てのセルを表すRangeオブジェクトを返す
定義 Function SpecialCells(Type As XlCellType, [Value As XlSpecialCellsValue]) As Range

SpecialCellsメソッドの引数

SpecialCellsメソッドに引数として指定できる項目は下表の通りですが、指定必須の引数はTypeのみです。

引数 説明
Type 型(必須)
取得するセルの種類をXlCellType列挙体で指定
Value
結果に含めるセルの種類をXlSpecialCellsValue列挙体で指定(※)(※)TypeがxlCellTypeConstantsまたはxlCellTypeFormulasの場合に指定。
XlSpecialCellsValue列挙体で指定するが、複数の値を加算して指定可能。
なお、省略時は全ての定数および数式が対象。

XlCellType列挙体、XlSpecialCellsValue列挙体

XlCellType列挙体およびXlSpecialCellsValue列挙体は、下表の通りです。

名前 説明
XlCellType列挙体
xlCellTypeAllFormatConditions -4172 表示形式が設定されているセル
xlCellTypeAllValidation -4174 条件の設定が含まれているセル
xlCellTypeBlanks 4 空白セル
xlCellTypeComments -4144 コメントが含まれているセル
xlCellTypeConstants 2 定数が含まれているセル
xlCellTypeFormulas -4123 数式が含まれているセル
xlCellTypeLastCell 11 使われたセル範囲内の最後のセル
xlCellTypeSameFormatConditions -4173 同じ表示形式が設定されているセル
xlCellTypeSameValidation -4175 同じ条件の設定が含まれているセル
xlCellTypeVisible 12 すべての可視セル
XlSpecialCellsValue列挙体
xlErrors 16 エラーのあるセル
xlLogical 4 論理値のあるセル
xlNumbers 1 数値のあるセル
xlTextValues 2 テキストのあるセル

■SpecialCellsメソッドを使用した、可視セル(表示されているセル)の操作

可視セル(表示されているセル)の操作を行いたい場合、SpecialCellsメソッドを使用すると便利です。

SpecialCellsメソッドの基本的な使用方法

SpecialCellsメソッドのType引数に「xlCellTypeVisible」を指定して、「Range.SpecialCells(xlCellTypeVisible)」のように記述すると、可視セルだけのRangeオブジェクトを取得することができます。

取得したRangeオブジェクト(以下のコーディング例ではオブジェクト変数「rng」)は、Rangeオブジェクトの集合体となっており、「rng.Count」のようにCountプロパティを使用すると、「rng」に含まれるRangeオブジェクトの個数を取得できます。
また、「rng.Address」のようにAddressプロパティを使用すると、Rangeオブジェクトのセル範囲を取得できます。

  1. '宣言部
  2. Dim sht As Worksheet
  3. Dim rng As Range
  4. '処理部
  5. Set sht = ActiveSheet
  6. Set rng = sht1.UsedRange.SpecialCells(xlCellTypeVisible)

可視セルのRangeオブジェクト全体に対する操作を行う場合は、このまま「rng.Select」「rng.Copy」「rng.Cut」のように指定すれば良い訳ですが、個々のRangeオブジェクトに対して操作を行う場合は、「For Each~Next」命令を使用します。

  1. '宣言部
  2. Dim sht As Worksheet
  3. Dim rng As Range
  4. '処理部
  5. Set sht = ActiveSheet
  6. For Each rng In sht.UsedRange.SpecialCells(xlCellTypeVisible)
  7.     '個々のRangeオブジェクトに対する操作
  8. Next rng

SpecialCellsメソッドの使用例(1)~可視行(表示されている行)の操作

A1セルからJ10セルまでの範囲「Range(“A1:J10")」のうち、偶数行を非表示にした下図のようなワークシートを用意します。

A B C D E F G H I J
1
3
5
7
9

SpecialCellsメソッドで取得したRangeオブジェクトから、RowsプロパティでRangeオブジェクトを取出すと、行単位のRangeオブジェクトを取得できます。

  1. '宣言部
  2. Dim sht As Worksheet
  3. Dim rng As Range
  4. '処理部
  5. Set sht = ActiveSheet
  6. For Each rng In sht1.Range(“A1:J10").SpecialCells(xlCellTypeVisible).Rows
  7.    '行単位のRangeオブジェクトに対する操作
  8. Next rng

各行の特定の列に対して操作を行いたい場合は、「rng.Columns(列番号)」のようにColumnsプロパティを使用します。
但し、Columnsプロパティで列番号を指定すると、非表示の列も参照できてしまうので、注意が必要です。

SpecialCellsメソッドの使用例(2)~可視列(表示されている列)の操作

A1セルからJ10セルまでの範囲「Range(“A1:J10")」のうち、偶数列を非表示にした下図のようなワークシートを用意します。

A C E G I
1
2
3
4
5
6
7
8
9
10

SpecialCellsメソッドで取得したRangeオブジェクトから、ColumnsプロパティでRangeオブジェクトを取出すと、列単位のRangeオブジェクトを取得できます。

  1. '宣言部
  2. Dim sht As Worksheet
  3. Dim rng As Range
  4. '処理部
  5. Set sht = ActiveSheet
  6. For Each rng In sht1.Range(“A1:J10").SpecialCells(xlCellTypeVisible).Columns
  7.    '列単位のRangeオブジェクトに対する操作
  8. Next rng

各列の特定の行に対して操作を行いたい場合は、「rng.Rows(行番号)」のようにRowsプロパティを使用します。
但し、Rowsプロパティで行番号を指定すると、非表示の行も参照できてしまうので、注意が必要です。

SpecialCellsメソッドの使用例(3)~可視セル(表示されているセル)の操作

A1セルからJ10セルまでの範囲「Range(“A1:J10")」のうち、偶数行と偶数列を非表示にした下図のようなワークシートを用意します。

A C E G I
1
3
5
7
9

SpecialCellsメソッドで取得したRangeオブジェクトから、RowsプロパティやColumnsプロパティを指定せずにRangeオブジェクトを取出すと、セル単位のRangeオブジェクトを取得できます。

  1. '宣言部
  2. Dim sht As Worksheet
  3. Dim rng As Range
  4. '処理部
  5. Set sht = ActiveSheet
  6. For Each rng In sht1.Range(“A1:J10").SpecialCells(xlCellTypeVisible)
  7.    'セル単位のRangeオブジェクトに対する操作
  8. Next rng

全ての可視セルを順に取得することになるため、この例であれば、A1セル、A3セル、A5セル、A7セル、A9セル、C1セル・・・のようになりますので、セルの位置によって操作を分けたいような時には、以下のようにRnageオブジェクトのHiddenプロパティを使用した方が扱い易いかも知れません。

Hiddenプロパティは、行または列が非表示かどうかを判定するためのプロパティのため、例えば、「Cells(i,j)」が可視セルかどうかを判定するためには、「If (Rows(i).Hidden = False) And (Columns(j).Hidden = False) Then」のように判定する必要があります。

  1. '宣言部
  2. Dim sht As Worksheet
  3. Dim rng As Range
  4. Dim i As Integer
  5. Dim j As Integer
  6. '処理部
  7. Set sht = ActiveSheet
  8. For i = 1 To 10
  9.     For j = 1 To 10
  10.         If (sht.Rows(i).Hidden = False) And (sht.Columns(j).Hidden = False) Then
  11.             '「sht.Cells(i, j)」セルに対する操作
  12.         End If
  13.     Next j
  14. Next i

 

国本温子(著),緑川吉行(著),できるシリーズ編集部(著)
出版社:インプレス
発売日:2022/3/23
単行本(ソフトカバー):A5判/912ページ

大村あつし(著),古川順平(著)
出版社:技術評論社
発売日:2021/1/9
単行本(ソフトカバー):A5判/800ページ

高橋宣成(著)
出版社:技術評論社
発売日:2019/11/25
単行本(ソフトカバー):B5変形判/576ページ

覚え書

Posted by hides