ラベル セル操作 の投稿を表示しています。 すべての投稿を表示
ラベル セル操作 の投稿を表示しています。 すべての投稿を表示

VBA オートフィルタの件数表示



AutoFilterで抽出したデータ数をカウントする


VBAを使ってオートフィルタで抽出したデータ数をカウントする方法です。
また、表示されている最終行番号も取得します。

関数、メソッドなど
XlCellTypeの定数を使用します。
Range.SpecialCells(xlCellTypeVisible).Count
指定したセルの範囲(Range)に対して表示されているセル(xlCellTypeVisible)をカウントします。


サンプルコード
Sub autosu()
Dim r As Long '最終行用
Dim countdata As Long 'データ数カウント用

'最終行を取得
r = Cells(Rows.Count, 1).End(xlUp).Row

'B列が東証1部のものを抽出
Range("a1").AutoFilter Field:=2, Criteria1:="東証1部"

'表示されているデータ数を取得
countdata = Range(Cells(2, 1), Cells(r, 1)).SpecialCells(xlCellTypeVisible).Count

    MsgBox countdata

End Sub


解説
まずはテーブルにある最終行番号を取得し、変数rに格納します。
オートフィルタでデータ抽出後にテーブル範囲全体行に対して、表示されている行数をカウントします。

ただ、抽出されたデータ数が0の場合、

「実行時エラー'1004';
 該当するセルが見つかりません。」

というエラーが出てしまいます。
これを回避するために

Range.SpecialCells(xlCellTypeLastCell)

で、抽出されたデータの最終行を調べてから分岐させます。

Sub autosu2()
Dim r As Long '最終行用
Dim r1 As Long
Dim countdata As Long 'データ数カウント用

'最終行を取得
r = Cells(Rows.Count, 1).End(xlUp).Row

'B列が東証1部のものを抽出
Range("a1").AutoFilter Field:=2, Criteria1:="東証3部"

'表示されているデータの最終行を取得
r1 = Range("A1").SpecialCells(xlCellTypeLastCell).Row


If r1 = 1 Then
    countdata = 0
Else
    '表示されているデータ数を取得
    countdata = Range(Cells(2, 1), Cells(r, 1)).SpecialCells(xlCellTypeVisible).Count
End If


    MsgBox countdata

End Sub




関連・類似ページ
XlCellType列挙
値の入っているセルのみを選択
最終セルの取得

VBA データの入力規則の設定



データの入力規則


VBAを使ってセルの入力規則を設定する方法です。
エクセル上だと[リボン]→[データ]→[データの入力規則]→・・・と同じ動作を再現します。


関数、メソッドなど
Range.Validationプロパティを使用し、Validationオブジェクトを設定します。
Range.Validation.Add(Type, AlertStyle, Operator, Formula1, Formula2)
最初設定するにはAddメソッドを使用します。

サンプルコード
Sub kisoku()
    'セル(I1:I4)に入力規則設定
    With Range(Cells(1, 9), Cells(4, 9)).Validation
        .Add Type:=xlValidateList, _
             Operator:=xlEqual, _
             Formula1:="=$A$1:$A$7"
    End With
End Sub


エクセルでの同じ設定をした場合は↓のような感じです。


解説
上記サンプルはリスト型で同一シート内にあるA1:A7の範囲を入力規則になるよう設定しています。 Validation.Addで入力規則の設定を開始しており、
Type:=は「リスト」や「整数」などのタイプの指定
Operator:=は「等しい」や「以上」など値の範囲などの指定
Formula1:=は元の値になるデータを選択しています。

Type以降に指定するプロパティはTypeプロパティの値によって異なります。

○Type 列挙
名前 内容
xlValidateCustom ユーザー設定 7
xlValidateDate 日付 4
xlValidateDecimal 小数点数 2
xlValidateInputOnly すべての値 0
xlValidateList リスト 3
xlValidateTextLength 文字列(長さ指定) 6
xlValidateTime 時刻 5
xlValidateWholeNumber 整数 1

種類です。


○Operator 列挙
名前 内容
xlBetween 二つの数式の範囲 1
xlNotBetween 二つの数式の範囲外 2
xlEqual 等しい 3
xlNotEqual 等しくない 4
xlGreater より大きい 5
xlLess より小さい 6
xlGreaterEqual 以上 7
xlLessEqual 以下 8

値の範囲です。


○AlertStyle 列挙
名前 内容
xlValidAlertInformation 情報 3
xlValidAlertStop 停止 1
xlValidAlertWarning 注意 2

エラーメッセージのアイコンです。



○Formula1、Formula2は必要に応じて値を入力します。



データの入力規則の削除

メソッドなど
Range.Validation.Delete


サンプルコード(パスワード設定なし)
Sub kisoku_del()
    'セル(I1:I4)に入力規則削除
    With Range(Cells(1, 9), Cells(4, 9)).Validation
        .Delete
    End With
End Sub


VBA セルをダブルクリックしたらイベントを実行する




ステートメント構文など
Worksheet_BeforeDoubleClick

VBAを使ってエクセル上でセルをダブルクリックしたら何かしらVBAで処理をする方法です。
コードを記述する場所は[Microsoft Excel Objects]の中の実行したいワークシート(Sheet1(Sheet1))などです。


サンプルコード
Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    'セルのA2:A5の範囲がダブルクリックされた時を対象
    If Not Intersect(Target, Range("A2:A5")) Is Nothing Then
        '処理例~~
        Cells(Target.Row, 2).Select
        MsgBox (Cells(Target.Row, 3))
    End If

End Sub



実行結果イメージ(セルA3をダブルクリックしています)


解説
Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
セルをダブルクリックしたときに発生するイベントです。
クリックしたセルが引数[Target]となります。

If Not Intersect(Target, Range("A2:A5")) Is Nothing Then
Range("A2:A5")の範囲の場合を対象として処理をしようとしています。
Intersectは「横切る」の意味で「指定した範囲ではなくない場合ならば」という2重否定になっています。


引数[Target]にはクリックした行や列の情報が入っているので
Target.Row
で行番号が
Target.Column
で列番号が取得できます。


Cells(Target.Row, 2).Select
は一応クリックしたセルが編集モードにならないように隣のセルを選択しています。


関連・類似ページ
プロシージャを呼び出す

関連にある「Callステートメント」と併用することで色々と用途の幅は広がると思います。

VBA 文字の改行




ステートメント構文など
vbCrLf

VBAを使ってエクセル上で、もしくはフォームやメッセージボックスなどで改行する方法です。
エクセル上だとセルの編集中に[Alt+Enter]と同じ動作を再現します。


サンプルコード
Sub kaigyo()
    Cells(1, 1) = "かいぎょう" & vbCrLf & "テスト1"
    Cells(1, 2) = "かいぎょう" & vbLf & "テスト2"
    Cells(1, 3) = "かいぎょう" & vbCr & "テスト3"
    Cells(1, 4) = "かいぎょう" & Chr(10) & "テスト4"
    Cells(1, 5) = "かいぎょう" & Chr(13) & "テスト5"
    Cells(1, 6) = "かいぎょう" & vbNewLine & "テスト6"
    
    Debug.Print "かいぎょう" & vbCrLf & "テスト1"
    Debug.Print "かいぎょう" & vbLf & "テスト2"
    Debug.Print "かいぎょう" & vbCr & "テスト3"
    Debug.Print "かいぎょう" & Chr(10) & "テスト4"
    Debug.Print "かいぎょう" & Chr(13) & "テスト5"
    Debug.Print "かいぎょう" & vbNewLine & "テスト6"

End Sub


実行結果イメージ



解説
vbLf(定義済みの定数)
Chr(10)(実際の値)
この二つは「ラインフィード」の略で原則的には1行下に移る動作をします。

vbCr(定義済みの定数)
Chr(13)(実際の値)
この二つは「キャリッジ・リターン」の略で原則的にカーソルなどを同一行の先頭位置に移動します。

ラインフィードとキャリッジ・リターンを合わせて
vbCrLf (定義済みの定数)
Chr(13)&(Chr(10)実際の値)
で改行を表すのが通常っぽいですが
VBA上であれば
vbNewLine (定義済みの定数)
で思うような動作が得られると思います。


関連・類似ページ
セルの文字列置換Replace

VBAでMysqlのタイムスタンプのようなもの



MySQLのTIMESTAMPである'2015-03-17 14:16:15'のような書式をエクセル上で作成する方法です。

使用する関数はDate,Time, Format, NumberFormatLocal です。



単純にDate関数,Time関数を使うと…
Sub TimeStamp()

    Cells(2, 2) = Date
    Cells(3, 2) = Time

End Sub



エクセルのデフォルトの表記だとこんな感じになります。(環境によって異なる結果になると思います。)


Format関数を噛ませると
Sub TimeStamp()

    Cells(2, 2) = Date
    Cells(3, 2) = Time

End Sub


エクセル上では同じように見えます。

なのでエクセルでも"yyyy-mm-dd hh:mm:ss"と表記したい時はまずはNumberFormatLocalプロパティを使ってセルの書式を変える手間が必要になります。
Sub TimeStamp()
    Cells(2, 2) = Format(Date, "yyyy-mm-dd")
    Cells(3, 2) = Format(Time, "hh:mm:ss")

    Cells(4, 2).NumberFormatLocal = "yyyy-mm-dd hh:mm:ss"
    Cells(4, 2) = Format(Date, "yyyy-mm-dd") _
     & " " & Format(Time, "hh:mm:ss")
End Sub




こんな感じになります。



特に問題なく動きますが時間の分の部分は"mm"じゃなくて"nn"の方が好ましいようです。

VBA 数値から文字列への変換




エクセル上で=text(cells,0)で変換できるのであまり需要はないかもしれないですが、
VBAを使って数値から文字列への変換を無理矢理気味にやってみました。

選択範囲セルで動くサンプルです。

○サンプルコード[text( ,0)]
Sub format_moji1()
    
    '選択範囲のセルの書式を文字列に変更
    Selection.NumberFormatLocal = "@"
    
    'それぞれのセルで処理
    Dim x As Range
        '一文字目が0だと取れてしまうので回避
        If Left(x, 1) <> 0 Then
            '指数化してしまうものを回避
            If InStr(x, "E") = 0 Then
                'ワークシート関数で…
                x = WorksheetFunction.Text(x, 0)
            End If
        End If
    Next
    
    'ブランクのセルに”0”が表示されてしまうのでブランクに再変換
    Selection.Replace What:="0", Replacement:="", LookAt:=xlWhole
    
   

End Sub


参照・類似ページ
NumberFormatLocal
Replace



○サンプルコード[sendkeys]
Sub format_moji2()
Dim r As Long, c As Long, i As Long, j As Long

    '選択範囲のセルの書式を文字列に変更
    Selection.NumberFormatLocal = "@"

    '選択範囲の列の数
    c = Selection.Columns.Count
    '選択範囲の行の数
    r = Selection.Rows.Count
    
    For i = 1 To c '列繰り返し
        For j = 1 To r '行繰り返し
            
            '編集のためF2キー
            Application.SendKeys "{F2}"
            
            'セルの書式=文字列反映
            Application.SendKeys "{Enter}"
            
        Next j
    Next i
    
    
End Sub





参照・類似ページ
SendKeysメソッド




速度的にはWorksheetFunction.Text関数の方が断然早いっぽいです。

セルの選択位置の変更 Selectメソッド、 Gotoメソッド




Select

メソッド構文など
Range .Select

エクセルのワークシート上で選択されているセルを変更するメソッドです。
Range に選択するワークシートのセルを指定します。


サンプルコード

Sub SampSele()
 'セルB2を選択
 Worksheets(1).Range("b2").Select
End Sub

注意点としてはこのメソッドはアクティブなワークシートがアクティブな状態でないと

実行時エラー '1004':
RangeクラスのSelectメソッドが失敗しました。


とエラーが出るので
下記サンプルのような感じでActivateメソッドを使用しておく必要があります。

Sub SampSele()
 Worksheets(1).Activate
 Worksheets(1).Range("b2").Select
End Sub



また、Applicationオブジェクトのメソッドを使用して記述することも可能です。

Goto

メソッド構文など
Application.Goto(Reference, Scroll)

引数Referenceにセルの範囲を指定します。
引数Scrollは省略可能で(既定値:Flase)True OR False で選択したセルを左上にして画面をスクロールする場合にはTrueを指定します。


名前 説明 備考
Reference セル範囲 Range 必須
Scroll セルを画面左上に移動するか True or False 省略可(既定値:False)
引数一覧

サンプルコード
Sub SampGot()
 'セルB2を選択し、画面左上セルをB2に移動
  Application.Goto Reference:=Worksheets(1).Range("B2"), _
  Scroll:=True
End Sub

アプリケーションGotoメソッドだとワークシートのアクティブ等、状態は関係なく動作します。
(アクティブ出なかった場合は実行されたワークシートがアクティブになります。)

VBA セルの結合と結合の解除 Merge




構文など
セルを結合する結合は2通りあります。

Range .Merge
Range .MergeCells = True

1つ目がMergeメソッドで2つ目がMergeCellsプロパティです。
指定したセル範囲を結合します。


サンプルコード
'セルの結合をする(A1:B4)
Sub SampleMerge()
    Range("A1:B4").Merge
    Range(Cells(1, 1), Cells(4, 2)).Merge
    Range("A1:B4").MergeCells = True
    Range(Cells(1, 1), Cells(4, 2)).MergeCells = True
End Sub


四行サンプル書きましたがどれも結果は下記のようにA1:B4セルを結合します。




また、セルの結合の解除をする場合は下記の通りです。

Range .UnMerge
Range .MergeCells = False

元のセル範囲 Range が結合されていない状態でもエラーが出たりはしません。

セルに罫線を引く Bordersオブジェクト




構文など
Range .Borders.

セルに罫線を引くBordersオブジェクトです。
Excel機能にある「セルの書式設定」の「罫線」タブで設定出来る内容とだいたい同じです。
罫線を引く位置、スタイル、色はプロパティで設定します。





1.罫線を引く位置 Borders(index) プロパティ
名前 説明
xlEdgeBottom
xlEdgeLeft
xlEdgeRight
xlEdgeTop
xlDiagonalDown 左上から右下ななめ
xlDiagonalUp 右上から左下ななめ
xlInsideHorizontal 範囲の間の横
xlInsideVertical 範囲の間の縦
XlBordersIndex 列挙

Borders(index)を指定しない場合(省略した場合)はセルの周り全部が対象となります。

サンプルコード
Sub BorS1()

 'セル上部に罫線を引く
 Cells(2, 2).Borders(xlEdgeTop).LineStyle = xlContinuous
    
 'セル範囲に罫線を引く
 Cells(4, 4).Borders.LineStyle = xlContinuous

End Sub


2.罫線の太さ Weight プロパティ
名前 説明
xlHairline 極細 1
xlThin 細め 2
xlMedium 普通 -4138
xlThick 太い 4
XlBorderWeight 列挙

サンプルコード
Sub BorS2()
 'セル範囲に罫線を引く
   '細い
    Cells(2, 2).Borders.Weight = xlHairline
   'ふつう
    Cells(4, 2).Borders.Weight = xlThin    
   '太め
    Cells(6, 2).Borders.Weight = xlMedium  
   '太い
    Cells(8, 2).Borders.Weight = xlThick
End Sub




3.罫線のスタイル LineStyle プロパティ
名前 説明
xlContinuous 実線 1
xlDot 点線 -4118
xlDash 破線 -4115
xlDashDot 一点鎖線 4
xlDashDotDot ニ点鎖線 5
xlDouble 2 本線 -4119
xlSlantDashDot 斜破線 13
xlLineStyleNone 線なし -4142
XlLineStyle 列挙

サンプルコード
Sub BorS3()
 'セル範囲に罫線を引く
  Cells(2, 2).Borders.LineStyle = xlContinuous
  Cells(2, 4).Borders.LineStyle = xlDot
  Cells(2, 6).Borders.LineStyle = xlDash
  Cells(2, 8).Borders.LineStyle = xlDashDot
        
  Cells(4, 2).Borders.LineStyle = xlDashDotDot
  Cells(4, 4).Borders.LineStyle = xlDouble
  Cells(4, 6).Borders.LineStyle = xlSlantDashDot
  Cells(4, 8).Borders.LineStyle = xlLineStyleNone
End Sub





4.罫線の色 Color プロパティ

RGB(XX、XX、XX)、10進数、16進数で色を指定出来ます。
サンプルはRGBで指定しています。

サンプルコード
Sub BorS4()
 'セル右に緑線
 Cells(2, 2).Borders(xlEdgeRight).Color = RGB(0, 255, 0)
 'セル上に赤線
 Cells(2, 4).Borders(xlEdgeTop).Color = RGB(255, 0, 0)
 'セル下に青線
 Cells(2, 6).Borders(xlEdgeBottom).Color = RGB(0, 0, 255)
 'セルに黄線
 Cells(2, 8).Borders.Color = RGB(255, 255, 0)

 'セル左に紫
 Cells(4, 2).Borders(xlEdgeLeft).Color = RGB(128, 0, 128)
 'セル左上~右下に黒
 Cells(4, 4).Borders(xlDiagonalDown).Color = RGB(0, 0, 0)
 'セル右上~左下にライトグリーン
 Cells(4, 6).Borders(xlDiagonalUp).Color = RGB(144, 238, 144)

 'セル範囲の間縦にアクアブルー
 Range(Cells(6, 1), Cells(6, 3)).Borders(xlInsideVertical) _
 .Color = RGB(0, 255, 255)
 'セル範囲の間横にグレー
 Range(Cells(6, 4), Cells(7, 7)).Borders(xlInsideHorizontal) _
 .Color = RGB(128, 128, 128)
End Sub

ウインドウ枠の固定 FreezePanesプロパティ




プロパティ構文など
Window.FreezePanes = True

エクセルシート上でウインドウ枠の固定をする方法です。
エクセル2007以降だとツールバー「表示」ウインドウ枠の固定コマンドと同じで、エクセル2003だとツールバー「ウインドウ」ウインドウ枠の固定の動作と同様です。
エクセル上だと複数シートに対しての設定は出来ないですが、上記マクロのWindowにそれぞれのWorksheet(シート)を指定することど一括で設定出来たりします。

WindowオブジェクトのプロパティでActiveWindow.FreezePanes = Trueのように使います。


サンプルコード
'選択されている複数シート(Worksheet)を一括でウインドウ枠の固定をする
Sub WFtes()
 Dim ws As Worksheet

 '選択されているシート毎に処理をする
  For Each ws In ActiveWindow.SelectedSheets
   'セル(B2)を選択
    Cells(2, 2).Select
    ActiveWindow.FreezePanes = True

  Next
End Sub
すでにウインドウ枠の固定が設定されている場合に、さらにActiveWindow.FreezePanes = Trueを実行しても 固定されているセルの場所は変更されません。
固定セル位置を変更する場合には一度ActiveWindow.FreezePanes = Falseを実行して一旦ウインドウ枠の固定の解除の処理をすることが必要になります。

セルの並び替え Range.Sort メソッド



Range.Sort(Key1, Order1, Key2, Type, Order2, Key3, Order3, Header, OrderCustom, MatchCase, Orientation, SortMethod, DataOption1, DataOption2, DataOption3)

ExcelVBAでエクセル上のセルの並び替えをするRangeオブジェクトのメソッドです。
並び替えの対象ととする列(ソートキー)は引数で3つまで選択出来ます。
Rangeの部分に並び替えをする範囲を指定します。


○引数一覧
名前 説明 備考
Key1 ソートキー1
(Rangeか並び替え範囲
1行目のフィールド名)
Variant 必須
Order1 Key1の並び替え順 XlSortOrder xlAscending(昇順)
xlDescending(降順)
key2 ソートキー2
(Rangeか並び替え範囲
1行目のフィールド名)
Variant
Order2 Key2の並び替え順 XlSortOrder xlAscending(昇順)
xlDescending(降順)
Key3 ソートキー3
(Rangeか並び替え範囲
1行目のフィールド名)
Variant
Order3 Key3の並び替え順 XlSortOrder xlAscending(昇順)
xlDescending(降順)
Header 最初の行がヘッダーかどうか XlYesNoGuess xlGuess(エクセルに判断させる)
XlYes(1行目ヘッダーとする)
xlNo(既定値:一行目も並び替え対象)
OrderCustom Key1並び順の昇順降順以外の設定 Long 1(既定値)
2以降はユーザー設定リストに対応
MatchCase 大文字・小文字の区別 True or
False
する
しない(既定値)
Orientation 並び替えの方向 XlSortOrientation列挙 xlSortColumns(既定値:縦方向)
=xlTopToBottom
xlSortRows(横方向)
=xlLeftToRight
SortMethod 並べ替えの種類(言語による) XlSortMethod 列挙 xlStroke(総画数)
xlPinYin(既定値:フリガナ(音読))
DataOption1 Key1の文字列並び替え方法 XlSortDataOption 列挙 xlSortTextAsNumbers (文字列を数値とみなす)
xlSortNormal(既定値:文字列と数値は別として扱う)
DataOption2 Key2の文字列並び替え方法 XlSortDataOption 列挙 xlSortTextAsNumbers (文字列を数値とみなす)
xlSortNormal(既定値:文字列と数値は別として扱う)
DataOption3 Key3の文字列並び替え方法 XlSortDataOption 列挙 xlSortTextAsNumbers (文字列を数値とみなす)
xlSortNormal(既定値:文字列と数値は別として扱う)
・引数はKey1以外は省略可能です。が、省略すると各引数は保存されている値で処理されるのである程度指示してあげるのが好ましいとおもいます。
・Key1~3の優先順位はKey1>Key2>Key3となります。
・OrderCustomを指定するには、ユーザー設定リストを作成しておくことが必要になります。
 設定方法はエクセル上で並び替えの画面を表示し(データ→並び替え)、順序の項目欄でユーザー設定リストの「新しいリスト」というところで任意の文字列を記入します。


ピボットテーブルの並び替えは省略してます。


○サンプルコード
 
Sub RStest()
    '並び替え対象範囲(A1~H20)
    Range("A1:H20").Sort _
    key1:=Range("B1"), order1:=xlAscending, _
    key2:="順位", order2:=xlDescending, _
    key3:=Range("C1"), Order3:=xlAscending, _
    Header:=xlGuess, _
    OrderCustom:=1, _
    MatchCase:=True, _
    Orientation:=xlTopToBottom, _
    SortMethod:=xlPinYin, _
    DataOption1:=xlSortNormal
End Sub


↓はエクセル並び替え画面との対応画像です。





○エラー
複数シートをまとめてソートしていたらエラーが出ました。
ソートするシートはアクティブになっていないと、


並べ替えの参照が正しくありません。参照が並べ替えるデータ内にあることと「最優先されるキー」ボックスが空白でないことを確認してください。



とエラーメッセージが出ます。

ソートする前にシートをアクティブにすればエラー回避できるはずです。

重複した値を削除 RemoveDuplicatesメソッド



範囲.RemoveDuplicates(Columns, Header)

範囲内で指定した行Columnsに重複する値があれば、その行を削除するRangeメソッドです。
Excel2007以降でしか使用出来ません。

名前 説明 備考
Columns 範囲で指定した中での列番号
(左から何列目か)
Header 範囲内1行目をヘッダーとして扱うかどうか
既定値はxlNo
xlYes[扱う] or
xlNo[扱わない] or
xlGuess[Excelに判断させる]
引数一覧

例(A1-H11の範囲で3列目(C列)で重複する値を削除する)
Sub RemDTes()
    
    ActiveSheet.Range("A1:H11").RemoveDuplicates Columns:=3, Header:=xlYes

End Sub

結果



Columnsをarrayを使用して配列扱いにした場合は、指定した列で全て一致した場合に削除されます。
※エクセル上やる場合のイメージ



例(A1-C50の範囲で1列目と2列目(AとB列)で共に重複する値を削除する)
Sub RemDTes2()
    ActiveSheet.Range("A1:C50").RemoveDuplicates Columns:=Array(1, 2), Header:=xlYes
End Sub

XlCellType列挙、XlSpecialCellsValue列挙 定数一覧



セルの状態に対して条件が合致したものを返すRange.SpecialCells メソッドでの指定できる引数のXlCellType列挙、XlSpecialCellsValue列挙の項目一覧です

名前 説明
xlCellTypeAllFormatConditions 条件付き書式が設定されているセル -4172
xlCellTypeAllValidation 入力規則が設定されているセル -4174
xlCellTypeBlanks 空白セル 4
xlCellTypeComments コメントが含まれているセル -4144
xlCellTypeConstants 定数が含まれているセル 2
xlCellTypeFormulas 数式が含まれているセル -4123
xlCellTypeLastCell 使われたセル範囲内の最後のセル 11
xlCellTypeSameFormatConditions 同じ表示形式が設定されているセル?? -4173
xlCellTypeSameValidation 同じ条件の設定が含まれているセル?? -4175
xlCellTypeVisible すべての可視セル 12
xlErrors エラーを含むセル
xlLogical 論理値を含むセル
xlNumbers 数値を含むセル
xlTextValues 文字列を含むセル

定数:数式ではない値
論理値:True or False
xlCellTypeSameFormatConditions、xlCellTypeSameValidationは使い方がよくわかりません。


関連
値の入っているセルのみを選択(xlTextValues)
空白セルを含む行(列)を削除(xlCellTypeBlanks)

値の入っているセルのみを選択(xlTextValues)



指定した範囲内で値が入っているセル(空白セル以外)を選択します。

Range.SpecialCells(xlTextValues).Select

例)使われたセル範囲内で値が入っているセルを選択
Sub Scx0()
    Cells.SpecialCells(xlTextValues).Select
End Sub

例)使われたセル範囲内で空白のセルを選択
Sub Scx0()
    Cells.SpecialCells(xlCellTypeBlanks).Select
End Sub

関連
空白セルを含む行(列)を削除(xlCellTypeBlanks)

その他、エクセルのジャンプ機能(Ctrl + G)と同様に.SpecialCellsの引数に他の定数を使用することで、特定のセルを選択することができます。
XlCellType、XlSpecialCellsValue一覧

VBA 空白行の削除




ステートメント構文など
SpecialCells(XlCellType)

任意に指定した範囲の空白行を削除して詰める方法です。
エクセル上だと「ジャンプ」→「セル選択」→「空白行」を選択と同じ動作から「行全体を削除」までを再現します。


サンプルコード
Sub kuhakusakujo()
    '例としてF列の空白セルを選択
    Columns("F").SpecialCells(xlCellTypeBlanks).Select
    '選択したセルがある行全体を削除
    Selection.EntireRow.Delete
    '選択状態の解除(一応)
    Application.CutCopyMode = False
End Sub


実行結果イメージ


解説
Columns("指定列").SpecialCells(xlCellTypeBlanks).Select
で、指定列にある空白行を選択しています。
空白以外にスペースなどがあると対象外になります。

Selection.EntireRow.Delete
は、選択しているセルがある行全体(entire)を削除しています。


関連・類似ページ
XlCellType列挙、XlSpecialCellsValue列挙 定数一覧

SpecialCellsは他にもいろいろ条件が指定できます。
上記関連ページを参照してみてください。

VBA オートフィルタ(autofilter)の使い方



Range.AutoFilter(Field, Criteria1, Operator, Criteria2, VisibleDropDown)

Rangeの部分にはオートフィルタを適用させる範囲のリストの左上のセルを表記します。
エクセルにある機能のオートフィルタと同様です。
Excelの97-2003までは1フィールドにおける複数条件は2つまでとなっています。
Excel2007以降で2つ以上の複数条件で抽出する場合は引数Operatorに定数xlFilterValuesを指定し、Criteria1の条件を配列としてArrayを用いて指定します。

また、異なるフィールド(列)に対して条件を設定する場合は、AutoFilterメソッドを2度連続して支持すれば複数フィールドに条件が設定できます。

名前 説明 備考
Field フィルタの対象となるフィールド番号 整数 範囲内の左端の列が"1"順に2,3・・・
Criteria1 抽出条件となる文字列(1) 文字列 省略可(既定値:全て対象)
Operator フィルタの種類、抽出条件 XlAutoFilterOperator
省略可(既定値:xlAnd)
Criteria2 抽出条件となる文字列(2)
(Criteria1とOperatorが必要)
文字列 省略可
VisibleDropDown オートフィルタのドロップダウン矢印
表示有無
True or
False
省略可(表示する)
(既定値:True)
引数一覧


例)オートフィルタの条件を2つ設定して該当するレコードのみのデータを作成
Sub AFTes()
Dim ws1 As Worksheet, ws2 As Worksheet
    
   'Worksheetをセット
    Set ws1 = ActiveWorkbook.Worksheets(1)
    Set ws2 = ActiveWorkbook.Worksheets(2)
    
    'オートフィルタ
    'セルA1から始まるリストで3列目に東証1部か東証2部を表示
    'Field:=3 3列目
    'Criteria1・Criteria2は条件
    'Operator:=xlOr は Criteria1・Criteria2の論理演算子OR
    ws1.Range("A1").AutoFilter Field:=3, Criteria1:="東証1部", _
    Operator:=xlOr, Criteria2:="東証2部"
    
    
    '表示されている部分のみコピーしてSheet2に貼り付け
    ws.Range("A1").CurrentRegion.Columns("A:H").Copy _
    Destination:=ws2.Range("A1")
    
End Sub



◯エラー
抽出する条件と合致するものがない場合には
「実行時エラー '1004':
RangeクラスのAutoFilterメソッドが失敗しました。」
というエラーが出るのでない場合のエラー処理等の記述が必要になる場合もあるかと思います。



解除方法
下記はオートフィルターモードを判定してフィルタを解除する方法です。
    If ActiveSheet.AutoFilter.FilterMode = True Then
        ActiveSheet.Range("a1").AutoFilter
    End If

クリアする方法
オートフィルターの状態のまま全てのフィルタリングをクリアにする方法です。
Sub autof()
    'アクティブシートのフィルタをクリアにする
    If ActiveSheet.FilterMode Then ActiveSheet.ShowAllData

End Sub

日付表示書式指定 Format関数




Format(対象,format) 年月日時間の表示形式を変更する関数です。
date関数、Now関数やTime関数と組み合わせて日付、時間の表示形式を指定します。
西暦、和暦等でも表示できます。



文字 説明 備考
/(スラッシュ) 年月日の区切り位置 -
:(コロン) 時間の区切り位置 -
年月日・時間の既定の表記 -
y 01/01から数えた日数 1~366
yy 西暦下2桁 0~99
yyyy 西暦4桁 0~9999
m 1~12
mm 01~12
mmm 月(英語表記略称3文字) -
mmmm 月(英語表記) -
d 1~31
dd 01~31
ddd 曜日(英語表記略称3文字) -
dddd 曜日(英語表記) -
h 時間 0~23
hh 時間 00~23
n 0~59
nn 00~59
s 0~59
ss 00~59
AM/PM
am/pm
AM・PM(am・pm)を表記
hと併用で0~12時間表記
00~59
ss 00~59
g 和暦年号ローマ字1文字 M、T、S、H
gg 和暦年号漢字1文字 明、大、昭、平
ggg 和暦年号漢字 明治、大正、昭和、平成
e 和暦年 1~99
ee 和暦年 01~99
q 四半期 (1月→1)
Long Date yyyy年mm月dd日 表記 -
Long Time hh:nn:ss 表記 -
Format一覧


Format関数での結果はエクセルのセル上で必ずしも表現されるわけではないようです。
違いは例を参照してください。


例)エクセルに表示(元のセルの書式は標準)
Sub YMDhms()
    'サンプル
    Range("A2") = Format(Date, "yyyy/mm/dd")
    Range("A3") = Format(Time, "hh:mm:ss")
    Range("A4") = Format(Date, "c")
    '日付
    Range("A5") = Format(Date, "y")
    Range("A6") = Format(Date, "yy")
    Range("A7") = Format(Date, "yyyy")
    Range("A8") = Format(Date, "m")
    Range("A9") = Format(Date, "mm")
    Range("A10") = Format(Date + 80, "mmm")
    Range("A11") = Format(Date, "mmmm")
    Range("A12") = Format(Date, "d")
    Range("A13") = Format(Date, "dd")
    Range("A14") = Format(Date, "ddd")
    Range("A15") = Format(Date, "dddd")
    '時間
    Range("A16") = Format(Time, "h")
    Range("A17") = Format(Time, "hh")
    Range("A18") = Format(Time, "n")
    Range("A19") = Format(Time, "nn")
    Range("A20") = Format(Time, "s")
    Range("A21") = Format(Time, "ss")
    Range("A22") = Format(Time, "AM/PM")
    Range("A23") = Format(Time, "am/pm hh:mm")
    '和暦
    Range("A24") = Format(Date, "g")
    Range("A25") = Format(Date, "gg")
    Range("A26") = Format(Date, "ggg")
    Range("A27") = Format(Date, "e")
    Range("A28") = Format(Date, "ee")
    Range("A29") = Format(Date, "q")
    'その他
    Range("A30") = Format(Date, "Long Date")
    Range("A31") = Format(Time, "Long Time")
End Sub



例)debug.printに表示
Sub YMDhms2()
    'サンプル
    Debug.Print Format(Date, "yyyy/mm/dd")
    Debug.Print Format(Time, "hh:mm:ss")
    Debug.Print Format(Date, "c")
    '日付
    Debug.Print Format(Date, "y")
    Debug.Print Format(Date, "yy")
    Debug.Print Format(Date, "yyyy")
    Debug.Print Format(Date, "m")
    Debug.Print Format(Date, "mm")
    Debug.Print Format(Date + 80, "mmm")
    Debug.Print Format(Date, "mmmm")
    Debug.Print Format(Date, "d")
    Debug.Print Format(Date, "dd")
    Debug.Print Format(Date, "ddd")
    Debug.Print Format(Date, "dddd")
    '時間
    Debug.Print Format(Time, "h")
    Debug.Print Format(Time, "hh")
    Debug.Print Format(Time, "n")
    Debug.Print Format(Time, "nn")
    Debug.Print Format(Time, "s")
    Debug.Print Format(Time, "ss")
    Debug.Print Format(Time, "AM/PM")
    Debug.Print Format(Time, "am/pm hh:mm")
    '和暦
    Debug.Print Format(Date, "g")
    Debug.Print Format(Date, "gg")
    Debug.Print Format(Date, "ggg")
    Debug.Print Format(Date, "e")
    Debug.Print Format(Date, "ee")
    Debug.Print Format(Date, "q")
    'その他
    Debug.Print Format(Date, "Long Date")
    Debug.Print Format(Time, "Long Time")
End Sub

セルの書式設定 NumberFormatLocalプロパティ



Range.NumberFormatLocal = "表示形式"

セル(Range、Cell)のプロパティです。
エクセル上での「セルの書式設定」で表示形式の分類を「ユーザー定義」の種類で指定するのと同様の処理です。
書式の設定をする方法は他にFormat関数がありますが、エクセル上で表現する場合はこのRange.NumberFormatLocalプロパティを使用するほうが思い通りに出来るとおもいます。

形式 説明 備考
% パーセント表示 "0"・"#"と使う 0.0%
@ 文字列 - @
. 小数点 "0"・"#"と使う 0.0
, 桁区切り "0"・"#"と使う #,###
: 日時 "h"・"m"・"s"と使う
1=24h
hh:mm:ss
/ 日付 "y"・"m"・"d"等と使う
1=1900/01/01
yy/mm/dd
(西暦年月日)
G/標準 標準 - G/標準
"任意指定文字" 指定文字 "" を & で結ぶ "ggge" & "年" & "mm" & "月" & "dd" & "日"
(和合年月日)
(その他) 1000円単位でマイナス赤字 - "
\
" & "#,###,;" & "
\
" & "[赤]-#,###,"
表示形式一覧

・"0"と"#"の違い
"0"は指定された桁に値がなければ"0"と表示されます。
"#"は指定された桁に値がなければ空白になります。
関連
数値表示書式指定 Format関数


Sub NFL()
    Range("a2").NumberFormatLocal = "0.0%"
    Range("a3").NumberFormatLocal = "@"
    Range("a4").NumberFormatLocal = "#,###.#"
    Range("a5").NumberFormatLocal = "0,000.0"
    Range("a6").NumberFormatLocal = "hh:mm:ss"
    Range("a7").NumberFormatLocal = "yy/mm/dd"
    Range("a8").NumberFormatLocal = "G/標準"
    Range("a9").NumberFormatLocal = "ggge" & "年" & "mm" & "月" & "dd" & "日"
    Range("a10").NumberFormatLocal = "\" & "#,###,;" & "\" & "[赤]-#,###,"
End Sub


結果

列幅を設定(ColumnWidth)と行の高さの設定(RowHeight)



Columns.ColumnWidth = 長さ
Rows.RowHeight = 長さ

長さに設定する数値は標準スタイルの 1 文字分フォントと同じサイズです。


例)A:B列を 長さ2.5 に変更する
Sub Columnwidth()
    
    Columns("A:B").Columnwidth = 2.5

End Sub


結果




◯エラー
※.Columnwidth.Width としてしまうと

「Range クラスの Width プロパティを設定できません。」

という実行時エラー '1004';が表示されてしまいます。

セル・行・列の挿入、セル・行・列の位置を変更する Insertメソッド



Range.Insert(Shift, CopyOrigin)

セルの挿入や列の挿入、行の挿入をするRangeメソッドです。
エクセル上でのショートカット[Ctrl + +(プラス)]の挿入と同一処理です。

引数を指定して挿入後に下か右のどちらかにシフトさせるか指示することが出来ます。
引数を指定しないとエクセルにシフト方向は判断させることになります。(行(Row)を選択してれば下側、列(Column)なら右側のような感じ)

・列の挿入 クリップボードに列がある場合はRangeの列に挿入され、指定された範囲にあったセルは右側にシフトされます。
クリップボードになにもない場合は空白列を挿入します。


サンプル1)列の挿入1
Sub SampleColI1()
    'C列をA列に挿入する
    Columns("C").Cut 'C列をカット
    Columns("A").EntireColumn.Insert 'A列左に挿入

    'D列に空白列を挿入する
    Range("D:D").Insert
End Sub

結果



サンプル2)列の挿入2
Sub SampleColI2()
    'セル"C1"を"A1"に挿入する
    Range("C1").Cut
    Range("A1").EntireColumn.Insert

    'D列に空白列を挿入す
    Range("D1").EntireColumn.Insert
End Sub

結果