VBA

セル操作

列を挿入する(書式引継ぎ)

Range("A7").Entirecolumn.Insert
CopyOrigin:=xlFormatForm
Right Or Below

Insertで挿入、その後CopyOrigin:=xlFormatFormを指定することで元の書式を引き継ぎます。

アウトラインのグループ化

Rows.Group

グループ化はGroupと記載します。

アウトライングループ化の解除

Rows.Ungroup

Ungroupとすることでグループ化を解除します。

セルのデータをクリアする

Range("A2:A7").ClearContents

全て(値、書式)のデータをクリアします。

Range("A2:A7")=""

またはもっと簡単に空白を指定すれば表示は消えます。

行全体を消去する

Range(Activecell.Offset:=Count).EntireRow.Delete

EntireRowで1行を選択しDeleteで消去します。

ウインドウ枠を固定する

Range("B2").Activate
Activewindow.FreezePanes=True

注意すべきポイントは先にActiveにしておかないとエラーになります。まずActiveにしてから指定します。

セルの値でフィルターをかける

With sheets("読み込み").Range("C1").AutoFilter Field:=1,Criteria1:=Range("品名！A2").Value
.Range("C1").CurrentRegion.Copy
sheets("チェックシート").Range("A1").PasteSpecial Paste:=xlpastevalues.AutoFilter=False
End width
Application.CutCopyMode=False

AutoFilterでフィルターをかけます。Field:=1で"",Criteria1:=で"",フィルターをかける値を指定します。

オートフィルタ設定

Range("A1").AutoFilter

値を設定せずにまずフィルターだけ表示させることも出来ます。

特定のセルを含むセル範囲を取得する

Activecell.CurrentRegion.select
MsgBox Range("C1").CurrentRegion.Address

例ではActivecellを含む全体をCurrentRegionで取得しselectで選択しています。

セルに格子罫線を引く

Range("A3:A5").Borders.LineStyle:=xlCountinous

Bordersで線を引くことを指定しLinStyleでどのように線を引くか指定します。

条件が一致した位置に列を挿入する

Dim Foundcell As Range
Set Foundcell=Range("1:1").Find("6月")
If Not Foundcell Is Nothing Then
    Foundcell.Offset(0,1).Entirecolumn.Insert
End If

Findで6月を指定し、If条件で6月が見つかった場合に列挿入を指定しています。

重複行の削除

Worksheets(1).Range("A:C").RemoveDuplicates
Rows:=Array(1,2,3).Header=xlYes

RemoveDuplicatesを記載することにより範囲内に同一の文字列があった場合1つの行を残して他行を消去します。また、HeaderをNoにすると1行目は見出しとして認識されません。

アクティブセルより上の行を削除する

Range("A1:A"& Activecell.Row-1).EntireRow.Delete

Row-1とすることで1行上を指定していることになります。+1にすれば1行下になります。

昇順で行並び変え

Range(セル範囲).Sort Key1:=Range("A1"),Order1:=xlAscending

Order1:=xlAscendingで昇順指定

降順で行並び変え

Range(セル範囲).Sort Key1:=Range("A1"),Order1:=xlDescending

Order1:=xlDescendingで降順指定

コピーモードを終了する

Application.CutCopyMode=False

CutCopyMode=Falseでコピー枠表示は消えます。

列幅を指定する

Range("B:C").ColumnWidth=25

ColumnWidth=数字を入れることにより任意の列幅に変更できます。

1行おきにセルに色を付ける①

Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression,Formura1:="=MOD(Row(),2)=0"
Selection.FormatConditions(1).Interior.ColorIndex=35

=MOD(Row(),2)=0という計算式を入れ条件に一致する行のみSelection.FormatConditionsに追加していき、そして最終的にSelection.FormatConditionsに追加された行のみ色を指定しています。

1行おきにセルに色を付ける②

Dim i As Long
Dim 最終行 As Long
Dim 最終列 As Long
最終行=Cells(Rows.Count,1).End(xlUp).Row
最終列=Cells(1,Columns.Count).End(xlToLeft).Column

For i=1 To 最終行 Step2
    Range(Cells(i,1),Cells(i,最終列)).Interior.color=15794160
Next

変数に列/行それぞれの最大値を取得格納しておき、For～Next内でStep2ずつ(つまり1行おき)に色付けしています。慣れれば①の方法より簡単です。

高速で行削除

Dim ListLastRow As Long
Dim Delete Rows As Range
Dim ws As Worksheet

Set ws=Activesheet
ListLastRow=ws.cells(Rows.Count,1).End(xlUp).Row

For i=2 To ListLastRow
    If IsEmpty(ws.cells(i,2))Then
    Set DeleteRows=ws.Rows(i).EntireRow
    Else
    Set DeleteRows=Union(DeleteRows,ws.Rows(i).EntireRow)
    End if
End If
Next

If Not DeleteRows Is Nothing Then
DeleteRows.Delete

1行ずつ削除していると時間がかかるため削除行をひとまとめにした後でまとめて削除しています。まとめるのはUnion関数です。

AdvancedFilterでフィルターをかけた範囲のみ操作する

Range("A1").CurrentRegion.specialcells(xlcellTypeVisible).Select
With Range("A1").CurrentRegion.Resize(,Rows.count-1).offset(1,0).specialcells(xlcellTypeVisible).Select

Resizeでフィルターかけた範囲のみに選択範囲を変更します。

AdvancedFilterの解除

Activesheet.showAllData

showAllDataで全てを表示します。

空白行を削除せずに上に詰める

Dim i As Integer,j As Integer
j=1
For i=2 To Range("B"& Rows.count),End(xlUp).Row
    If(Range("B"& i).Rows<>"")or
    (Range("D"& i).Rows<>"")Then
    j=j+1
    Range("B"& i).Resixe(,3).Copy
    Range("B"& j)
    Range("B"& i).Resize(,3)=""
    End if
Next

まずIf条件でRange("B"& i).Rows行の値が空白 or Range("D"& i).Rows行の値が空白の時、変数jに＋１しています。(空白行の1行下)Range("B"& i).Resixe(,3).Copyで空白行の1行下にコピーして元の行は空白に変更しています。