和風スパゲティのレシピ

日本語でコーディングするExcelVBA

トップ > Range・Cells > 第1行の書式を最終行までコピーする - Rows

第1行の書式を最終行までコピーする - Rows

Range・Cells サンプルコード 書式設定・表示設定 VBA

セルの書式を行ごとコピーする方法を解説します。

特にデータの第1行をデータの最終行までコピーするコードを紹介します。

基本コード

Sub 行の書式をコピーする()

    ' 書式を反映するデータエリア(コピー元含む)を取得
    Dim Rows書式反映行 As Range
    Set Rows書式反映行 = Worksheets("○○").Rows("4:100")
    
    ' コピー元には書式エリアの第1行を使用
    Dim Rowコピー元行 As Range
    Set Rowコピー元行 = Rows書式反映行.Rows(1)
    
    ' AutoFillメソッドの「書式のみコピー」を実行
    Rows書式反映行.Rows(1).AutoFill Rows書式反映行, xlFillFormats
    
End Sub

解説

書式のコピーには「PasteSpecial(形式を選択して貼り付け)」が思い浮かびますが、
行ごとコピーする場合はAutoFillメソッドが使用できます。

クリップボードを介さないため安全で高速ですので、
フィルハンドルをイメージできるコピーにはこちらを使用しましょう。


AutoFillメソッドは第2引数でフィルハンドルオプションを指定でき、
下記の画像の各種設定でコピーを行うことができます。

フィルハンドルダイアログ

定数名 値 内容
xlFillDefault 0 Excelによる自動判定
xlFillCopy 1 セルのコピー
xlFillSeries 2 連続データ
xlFillFormats 3 書式のみコピー
xlFillValues 4 書式なし(値のみ)コピー
xlFillDays 5 連続データ(日単位)
xlFillWeekdays 6 連続データ(平日のみ)
xlFillMonths 7 連続データ(月単位)
xlFillYears 8 連続データ(年単位)
xlLinearTrend 9 連続データ(加算推定)
xlGrowthTrend 10 連続データ(乗法推定)
xlFlashFill 11 フラッシュフィル

今回は「書式のみコピー」のため、xlFillFormatsを使用しました。


なお、AutoFillメソッドはコピー元.AutoFill コピー先という構文ですが、
コピー元がコピー先の第1行(を含むエリア)になっている必要があります。

このため、上記の基本コードのように、

  • まずは反映エリアを取得する
  • 反映エリア.Rows(1)をコピー元として使用する

という手順でコーディングするのが簡単です。

データエリアの取得方法

サンプルでは「Rows("4:100")」とベタ打ちしましたが、
実際はここを動的に取得する必要があります。

その際、Range(Rows(), Rows())形式では非常に手間なので、
データ範囲取得メソッドとEntireRowを組み合わせるのがおすすめです。

' 表エリア.EntireRowを取得
Dim Rows書式反映行 As Range
Set Rows書式反映行 = Worksheets("○○").Range("A4").CurrentRegion.EntireRow
Set Rows書式反映行 = Worksheets("○○").AutoFilter.Range.EntireRow
Set Rows書式反映行 = Worksheets("○○").ListObjects(1).Range.EntireRow

' 見出しがいらない場合は下にひとつずらして行数を1減らす
Set Rows書式反映行 = Rows書式反映行.Offset(1)
Set Rows書式反映行 = Rows書式反映行.Resize(Rows書式反映行.Rows.Count - 1)

' ※ テーブル(ListObject)で見出しを減らしたい場合はDataBodyRangeでOK

　
またそれ以外の方法として、反映範囲の行数が分かっている場合は、

' 何らかの処理の後でデータ数が分かっているとする
Dim データ数 As Long: データ数 = 100

' その場合はコピー元行を指定
Dim Rowコピー元行 As Range
Set Rowコピー元行 = Worksheets("○○").Rows(4)
    
' コピー元をコピー元.Resize(データ数)へコピー
Rowコピー元行.AutoFill Rowコピー元行.Resize(データ数), xlFillFormats

このようにResizeプロパティを使えるため非常に簡単になります。

その時々に合わせて、読みやすく書きやすいコードを採用してください。

汎用関数化

今回の処理を行う方は、汎用関数化しておくと便利です。

' 書式のコピー
Sub 指定範囲の第1行書式を範囲全体へコピーする(セル範囲 As Range)
    セル範囲.Rows(1).AutoFill セル範囲, xlFillFormats
End Sub

' 見出し行のカット
Function セル範囲の上部をn行除外(元のセル範囲 As Range, n As Long) As Range
    Set セル範囲の上部をn行除外 = 元のセル範囲.Offset(n).Resize(元のセル範囲.Rows.Count - n)
End Function

' 汎用関数使用サンプル
Sub 行の書式をコピーする()

    Call 指定範囲の第1行書式を範囲全体へコピーする _
        (セル範囲の上部をn行除外(Worksheets("○○").AutoFilter.Range, 1))

End Sub

今回のコードが1行で実行できるようになっており、
かなり読みやすく＆書きやすくなっているのが分かりますね。

どちらかというと書式のコピーコードより、
セル範囲から第1行をカットするOffset&Resizeの方が便利な関数だったりします。


このように、「簡単だけど書くのが面倒なコード」は、
関数化が簡単な割に、汎用関数にするメリットが大きいです。

汎用関数集を作っている方は、ぜひそのメンバーに加えてあげてください。


汎用関数集の作り方・使い方についてはこちらをどうぞ。

www.limecode.jp