ＥＸＣＥＬ 空白を左詰め

 

•  

 

 

 

はじめに

Excelに他のテキストファイルからデータを貼り付けたりする際、列や行がバラバラな場合があり空白セルを削除して左詰め、上詰めしたい時があると思います。

私は仕事でよくExcel を使いますが、バッチ処理に発生したエラーログや処理実行後のログなどの解析を行う際にいったん Excel に貼り付けてそこから解析することがあります。

そんなときに他のファイルから貼り付けるために列がバラバラで解析しづらかったという苦い経験がありました。

しかし空白セルのみを削除し列や行を左詰め、上詰めすることでスイスイ解析できるようになりました。

本当に便利でかつ速攻でできるので、ぜひこの記事を読んで参考にしてみてください！

空白セルのみを削除し左詰め、上詰めする方法

まず空白セルを削除したいデータを選択し、「Ctrl」+ 「G」と入力します。

すると「ジャンプ」画面が表示されますでの、「セル選択（S)」をクリックします。

「選択オプション」画面が表示されますので、「空白セル(K)」を選択し「OK」ボタンを押下します。

すると、下記のように空白セルのみが選択されます。

この時、選択したセル範囲が多いと時間がかかりますが、それでも気長に待つと空白セルがすべて選択されます。

次に「Ctrl」+ 「-」を入力してください。すると削除した際にセルを「左方向にシフト」させるか（つまり左詰め）「上方向にシフト」（上詰め）するか聞かれますので、自分がしたい方向を選択します。

ここでは列を詰めたい（左に詰めたい）ので「左方向にシフト」を選択して「OK」ボタンを押下します。

そして画面を確認してみてください。空白セルが削除され左詰めされていることが確認できます！

表から数字だけ消す

表から数値だけを削除する

表内の数値だけを選択するには、「選択オプション」画面で「定数」を選択し、「数値」を指定します。このあとDeleteキーを押すと、数値だけを削除できます。

↑ 表を選択し、「ホーム」タブ → 「検索と選択」ボタン → 「条件を選択してジャンプ」をクリックします

「選択オプション」画面が表示されます。

↑ 「定数」を選択して、「数値」だけにチェックを入れ、「OK」ボタンをクリックします

表内で数値が入力されているセルがすべて選択されます。Deleteキーを押すと、数値が削除されます。

数値だけ削除する方法を使えば、似たような表を簡単に作成することができます。ぜひマスターして使ってみて下さい。

オートフィルタで変数を使ってフィルタする

【VBA】オートフィルタで変数を使ってフィルタする【表示形式に注意】

Excel VBA

Excel VBAで、「変数」を使ってオートフィルタをフィルタする方法について、ご紹介します。フィルタする値の「表示形式を同じ」にする必要があるので、注意です。実務では「変数」を使ってフィルタすることが多いので、「変数」を使ったオートフィルタの操作をマスターしていきましょう。

はじめに

この記事では、VBAで変数を使ってオートフィルタを操作する方法について、ご紹介します。

実務でVBAを使ってオートフィルタを操作する場合は、「変数」や「セルの値」を使ってフィルタする場合が多いです。

オートフィルタを使う場合には、「表の文字」と「フィルタする文字」を完全に一致させる必要があるので、注意です。

VBAを使ってオートフィルタの操作を自動化していきましょう。

では、VBAで「変数」を使ってオートフィルタを操作する方法について、解説していきます。

この記事で紹介すること

• VBAで変数を使ってオートフィルタを操作する方法

こちらの記事も参考になります

• オートフィルタの操作をマスタする
• オートフィルタの設定
• オートフィルタの解除
• オートフィルタの色フィルタを使う
• オートフィルタで日付をフィルタする
• オートフィルタで複数条件のフィルタ
• オートフィルタで空白以外や空白をフィルタ
• オートフィルタで変数を使ってフィルタ　←こちらの記事
• オートフィルタで作業列を使ってフィルタ
• オートフィルタのフィルタとソート
• オートフィルタの結果を判定
• オートフィルタの結果をコピーや削除する
• オートフィルタの結果を操作する

目次から見たい項目へ移動すると便利ですよ。

目次

• VBAコードまとめ
• オートフィルタで変数を使ってフィルタする
• 変数を使ってフィルタ
• セルの値を使ってフィルタ
• 変数とワイルドカードを使ってフィルタ
• 変数と比較演算子を使ってフィルタ
• オートフィルタで配列を使ってフィルタする
• 配列を使ってフィルタ
• セルに入力された値でフィルタ
• おわりに

VBAコードまとめ

オートフィルタを変数を使って操作するVBAコードについて、まとめています。

VBAコードだけを確認したい場合に、ご活用ください。

'【変数でフィルタ】

a = "600" '変数に数値を代入
Range("A1").AutoFilter 2, a '数値をフィルタ

a = "2021/8/1" '変数に日付を代入
Range("A1").AutoFilter 1, a '日付をフィルタ

'【セルの値でフィルタ】
Range("A1").AutoFilter 2, Range("D1") 'セルの値でフィルタ
Range("A1").AutoFilter 2, Format(Range("D1"), "#,##0万人") '表示形式を指定
Range("A1").AutoFilter 1, Format(Range("D1"), "yyyy/m/d") '表示形式を指定
Range("A1").AutoFilter 1, Format(Range("D1"), Range("A2").NumberFormatLocal) '表示形式を指定

'【ワイルドカード】

a = "*府"
Range("A1").AutoFilter 1, a '変数とワイルドカード

Range("A1").AutoFilter 1, "*" & Range("D1") 'セルの値とワイルドカード

'【比較演算子】

a = ">=2021/8/1"
b = "<=2022/1/1"
Range("A1").AutoFilter 1, a, xlAnd, b '変数

Range("A1").AutoFilter 1, ">=" & Range("D1"), xlAnd, "<=" & Range("E1") 'セルの値
    
'【配列】

ReDim a(1 To 3)
a(1) = "東京都"
a(2) = "千葉県"
a(3) = "北海道"
Range("A1").AutoFilter 1, a, xlFilterValues '配列でフィルタ

a = WorksheetFunction.Transpose(Range("A1:A3")) 'セルの値を取得
Range("A5").AutoFilter 1, a, xlFilterValues 'セルの値でフィルタ

では、解説していきます。

オートフィルタで変数を使ってフィルタする

オートフィルタで「変数」を使ってフィルタしてみます。

変数を使ってフィルタ

「変数」を使って数値をフィルタしてみます。

数値をフィルタ

変数に値を代入して、「.AutoFilter」を使うとフィルタできます。

Sub TEST1()
    
    '変数に数値を代入
    a = "600"
    
    '数値をフィルタ
    Range("A1").AutoFilter 2, a
    
End Sub

表を用意します。

では、VBAコードを実行してみます。

数値をフィルタできました。

日付をフィルタ

日付をフィルタする場合も同じで、変数に日付を入力して「.AutoFilter」でフィルタです。

Sub TEST2()
    
    '変数に日付を代入
    a = "2021/8/1"
    
    '日付をフィルタ
    Range("A1").AutoFilter 1, a
    
End Sub

表を用意します。

では、VBAコードを実行してみます。

日付をフィルタできました。

セルの値を使ってフィルタ

「セルの値」を使ってフィルタしてみます。

実務ではセルの値を使ってフィルタする、という方法をよく使います。

やり方は、変数を使う方法と一緒です。

ただ、「表の値」と「セルの値」で表示形式を一致させる必要があるので、注意です。

数字をフィルタ

セルに入力した値を使って数値をフィルタしてみます。

Sub TEST3()
    
    'セルの値でフィルタ
    Range("A1").AutoFilter 2, Range("D1")
    
End Sub

セルに数値を入力しておきます。

では、VBAコードを実行してみます。

「セルの値」を使って数値をフィルタできました。

なんの問題もなさそうです。

表示形式を設定するとちょっと事情が変わってきます。

表示形式が設定されている場合

表に表示形式を設定してみます。

先ほど使ったVBAコードを使ってみます。

Sub TEST4()
    
    'セルの値でフィルタ
    Range("A1").AutoFilter 2, Range("D1") '←表示形式を一致させる必要がある
    
End Sub

では、VBAコードを実行してみます。

「表の値」と「セルの値」で表示形式が違うので、うまくフィルタできていません。

表示形式を設定してフィルタ

セルから取得した値に、「表示形式を設定」してフィルタしてみます。

「Format」を使って表示形式を設定します。

Sub TEST5()
    
    'セルの値に表示形式を設定してフィルタ
    Range("A1").AutoFilter 2, Format(Range("D1"), "#,##0万人")
    
End Sub

では、VBAコードを実行してみます。

表示形式を設定してフィルタできました。

「.NumberFormatLocal」が便利

表示形式を設定する場合は、表の値から「.NumberFormatLocal」を使って表示形式を取得すると便利です。

Sub TEST6()
    
    'セルの値に表示形式を設定してフィルタ
    Range("A1").AutoFilter 2, Format(Range("D1"), Range("B2").NumberFormatLocal)
    
End Sub

では、VBAコードを実行してみます。

うまくフィルタできました。

日付をフィルタ

セルに入力した日付を使って、フィルタしてみます。

日付もなかなかクセがあります。

Sub TEST7()
    
    'セルの値でフィルタ
    Range("A1").AutoFilter 1, Range("D1") '表示形式を一致させる必要がある
    
End Sub

セルに日付を入力しておきます。

一見すると、同じ表示形式なので、フィルタできそうです。

では、VBAコードを実行してみます。

セルの値で日付をフィルタできていないです。

セルから値を取得すると、「2021/08/01」となって表示形式が違うので、フィルタできないです。

セルの値で日付をフィルタするために、表示形式を設定します。

表示形式を設定してフィルタ

表示形式を「"yyyy/m/d"」として、フィルタしてみます。

Sub TEST8()
    
    'セルの値に表示形式を設定してフィルタ
    Range("A1").AutoFilter 1, Format(Range("D1"), "yyyy/m/d")
    
End Sub

では、VBAコードを実行してみます。

表示形式を設定してフィルタできました。

「.NumberFormatLocal」が便利

日付の場合も、「.NumberFormatLocal」を使って、表示形式をフィルタされる値火r亜取得すると、便利です。

Sub TEST9()
    
    'セルの値に表示形式を設定してフィルタ
    Range("A1").AutoFilter 1, Format(Range("D1"), Range("A2").NumberFormatLocal)
    
End Sub

では、VBAコードを実行してみます。

「.NumberFormatLocal」を使ってフィルタできました。

こんな感じでピンポイントの値をフィルタする場合は、「表示形式」に注意です。

変数とワイルドカードを使ってフィルタ

変数とワイルドカードを使ってフィルタしてみます。

ワイルドカードの「*」を使ってみます。「〇〇を含む」フィルタをしたい場合に使えます。

変数でフィルタ

ワイルドカード含めて変数に入力して、フィルタします。

Sub TEST10()
    
    '変数にワイルドカードを代入
    a = "*府"
    
    '変数でワイルドカードを使ってフィルタ
    Range("A1").AutoFilter 1, a
    
End Sub

表を用意します。

では、VBAコードを実行してみます。

変数とワイルドカードを使ってフィルタできました。

セルの値でフィルタ

セルに入力された値を含む、という条件でフィルタをしてみます。

Sub TEST11()
    
    'セルの値を使ってワイルドカードでフィルタ
    Range("A1").AutoFilter 1, "*" & Range("D1")
    
End Sub

セルに値を入力します。

では、VBAコードを実行してみます。

「セルの値」と「ワイルドカード」でフィルタできました。

変数と比較演算子を使ってフィルタ

「変数」と「比較演算子」を使ってフィルタしてみます。

変数でフィルタ

「比較演算子」を「変数」に入力してフィルタしてみます。

Sub TEST12()
    
    '日付を変数に入力
    a = ">=2021/8/1"
    b = "<=2022/1/1"
    
    '変数で比較演算子を使ってフィルタ
    Range("A1").AutoFilter 1, a, xlAnd, b
    
End Sub

表を用意します。

では、VBAコードを実行してみます。

変数と比較演算子を使ってフィルタできました。

セルの値でフィルタ

「セルに入力された値」と「比較演算子」を使ってフィルタしてみます。

Sub TEST13()
    
    'セルの値と比較演算子を使ってフィルタ
    Range("A1").AutoFilter 1, ">=" & Range("D1"), xlAnd, "<=" & Range("E1")
    
End Sub

セルにフィルタしたい値を入力します。

では、VBAコードを実行してみます。

「セルの値」と比較演算子でフィルタできました。

オートフィルタで配列を使ってフィルタする

オートフィルタで「配列」を使ってフィルタしてみます。

配列を使うと、フィルタしたい値を自由にフィルタできます。

配列を使ってフィルタ

配列を作成しておいて、フィルタしてみます。

Sub TEST14()
    
    '配列に値を入力
    Dim a
    ReDim a(1 To 3)
    a(1) = "東京都"
    a(2) = "千葉県"
    a(3) = "北海道"
    
    '配列でフィルタ
    Range("A1").AutoFilter 1, a, xlFilterValues
    
End Sub

表を用意します。

表を用意

では、VBAコードを実行してみます。

配列でフィルタ

「配列」でフィルタできました。

セルに入力された値でフィルタ

セルに入力された値でフィルタしてみます。

Sub TEST15()
    
    'セルの値を一次元配列にして変数に格納
    a = WorksheetFunction.Transpose(Range("A1:A3"))
    
    '配列でフィルタ
    Range("A5").AutoFilter 1, a, xlFilterValues
    
End Sub

セルから値を取得すると、2次元配列になっているので、「WorksheetFunction.Transpose」で「一次元配列」に変換しているところが、ポイントです。

セルに値を入力しておきます。

セルに値を入力

では、VBAコードを実行してみます。

セルの値でフィルタ

セルの値でフィルタできました。

ＶＢＡ ＦＩＮＤメソッド使い方

• 「特定の範囲内からある値を含むセルを探したい」
• 「条件に当てはまるセルをまとめて操作したい」

と思うことはVBAを使っていればよくあります。

そんなときはFindメソッドが便利です。本記事では、条件に当てはまるセルを検索できるよう、Findメソッドの基本的な使い方や注意点を解説します。

 

目次

• 1 Findメソッドとは
  • 1.1 Findメソッドの引数
  • 1.2 Findメソッドの注意点
• 2 Findメソッドの使い方
  • 2.1 全文一致で検索
  • 2.2 部分一致で検索
  • 2.3 複数セルを検索
• 3 値が見つからないときの対策
• 4 まとめ

Findメソッドとは

Findメソッドとは、特定の範囲内（Rangeオブジェクト）から条件に当てはまるセルを検索します。条件に合ったセルが見つかればRangeオブジェクトを返し、見つからなければNothingが返ります。

1. Set range = Rangeオブジェクト.Find(条件に合わせた引数）

Findメソッドの引数

Findメソッドの引数は以下の通りです。数は多いですが、よく使うWhat、LookAt、SearchOrderの３つを覚えておきましょう。

• What（必須）: 検索するデータを指定します
• After: 検索開始のセルを指定。ここで指定したセルの次のセルから検索
• LookIn: 検索対象を設定。xlValuesは値、xlFormulasは数式、xlCommentsはコメント
• LookAt: 検索条件を指し、全文一致の場合はxlWhole、部分一致はxlPartを指定
• SearchOrder: 検索方向を指し、１列ごと検索する場合はxlByColumns、１行ごと検索する場合はxlByRowsを指定
• SearchDirection: 検索の向きを指定。xlNextは上から下、xlPreviousは下から上に検索
• MatchCase: 大文字と小文字を区別するかをTrue、Falseで指定
• MatchByte: 全角と半角を区別するかをTrue、Falseで指定
• SearchFormat: 書式検索するか否かをTrue、Falseで指定

引数を使ってFindメソッドを書くと下記のようになります。

1. Dim rng As Range
2.  
3. Set rng = ThisWorkbook.Worksheets(1).Range("A2:B9") _
4. .Find(What:="りんご", LookAt:=xlWhole, SearchOrder:=xlByRows)
5. MsgBox rng.Value

Findメソッドの注意点

Findメソッドの引数を省略すると、Excelファイル固有の検索設定が適用されるので注意しましょう。検索設定は、検索と置換ダイアログから確認できます。

また、引数を設定して実行すると、設定した値で検索設定が上書きされます。Findメソッドを使うときはこの特性に注意が必要です。

Findメソッドの使い方

続いてFindメソッドの基本的な使い方を見ていきます。条件の設定方法を覚えてFindメソッドを使いこなしましょう。

全文一致で検索

全文一致とは、検索対象の値と検索語の値がすべて一致することを指します。大文字・小文字は区別されません。全文一致で検索する場合はLookAt引数にxlWholeを指定します。

例えば、下記のような商品表からぶどうを探す場合を考えてみましょう。

ぶどうを完全一致で検索するときは下記のように書きます。商品表をRangeで指定して全文一致検索をしています。

1. Dim rng As Range
2.  
3. Set rng = ThisWorkbook.Worksheets(1).Range("A2:B9") _
4. .Find(What:="ぶどう", LookAt:=xlWhole)
5. MsgBox rng.Value

部分一致で検索

部分一致とは、検索対象の値に検索語が一部含まれる場合を指します。検索対象が「青りんご」だとして、「青」を部分一致で検索すると考えればわかりやすいでしょうか。

部分一致の場合はFindメソッドのLookAt引数にxlPartを指定します。下記商品表の青りんごを検索する場合で考えてみましょう。

青りんごの「青」で部分一致検索するには下記のように書きます。

1. Dim rng As Range
2.  
3. Set rng = ThisWorkbook.Worksheets(1).Range("A2:B9") _
4. .Find(What:="青", LookAt:=xlPart)
5. MsgBox rng.Value

複数セルを検索

条件に当てはまるセルが複数ある場合はどうでしょう。Findメソッドは最初に見つけた１つのセルしか返せませんが、FindNext、FindPreviousメソッドを使うと複数のセルを検索できます。

FindNext、FindPreviousメソッドともにFindメソッドで開始した検索を続行しますが、検索方向が異なります。FindNextは下方に、FindPreviousは上方に検索していきます。

例えば、下記商品表から「桃」と部分一致するセルを検索してみましょう。

FindNextを使って「桃」と部分一致するセルをダイアログ表示するには下記のように書きます。

1. Dim rng As Range
2. Dim firstAddress As String
3.  
4. ‘ 商品表をRangeで指定
5. With ThisWorkbook.Worksheets(1).Range("A2:B9")
6. Set rng = .Find("桃")
7. If Not rng Is Nothing Then
8. firstAddress = rng.Address
10. Do
11. MsgBox rng.Value
12. ‘ 前回値が見つかったセルの次から検索
13. Set rng = .FindNext(rng)
14. If rng Is Nothing Then
15. GoTo DoneFinding
16. End If
17. Loop While rng.Address <> firstAddress
18. End If
19. DoneFinding:
20. End With

より詳しくFindNextメソッドについて理解したい方はこちらの記事をご覧ください。

【Excel VBA入門】指定範囲から複数セルを検索するFindNextの使い方

値が見つからないときの対策

Findメソッドで検索した値が見つからないときはNothingが返ります。そのため、下記のように書いたときに値が見つからない場合、エラーが発生します。

1. Dim rng As Range
2.  
3. Set rng = ThisWorkbook.Worksheets(1).Range("A2:B9").Find(What:="りんご")
4. MsgBox rng.Value

このときrngにNothingが代入されますが、続けてメッセージボックスにValueプロパティで値を出力しています。NothingにはValueプロパティがないため、ここでエラーが発生します。

エラーを防ぐためにも検索した値が見つからない場合に備えましょう。下記の例では、条件分岐でNothingを判別しています。

1. Dim rng As Range
3. Set rng = ThisWorkbook.Worksheets(1).Range("A2:B9").Find("りんご")
4. If rng Is Nothing Then
5. MsgBox "検索した値が見つかりません"
6. Else
7. MsgBox rng.Value
8. End If

まとめ

findの基本的な使い方を解説してきました。注意点でも書きましたが、Excelファイル固有の検索設定に影響を受ける点に要注意。これを知らないと意図しない検索条件になってしまいます。

条件に当てはまる値を楽に検索できるFindメソッドを使ってExcel操作をより効率的にしていきましょう。

列を非表示にする

列を非表示にする
Columns("A").Hidden = True             'A列を非表示
Columns("A:D").Hidden = True           'A～D列を非表示
Range("A:A, D:E").EntireColumn.Hidden = True     'A、D、E列を再表示

Trueで非表示になります。

離れた複数の列を非表示にする場合、列はColumnの選択では非表示することができません。
Rangeで複数列を指定し、「EntireColumn」にすることで離れた複数列を非表示にすることができます。

セルで選んだ空白セルを０にする

Sub 選んだ所の空白を０にする()
    Range(“A1:E10”).SpecialCells(xlCellTypeBlanks).Value = 0 '選んだ所を０にする（Ａ１：Ｅ１０）
End Sub

オートフィルターで０以外を抽出

Sub オートフィルタで0以外を抽出する()

    ActiveSheet.Range("A1").AutoFilter Field:=5, Criteria1:="<>0"

End Sub