セルの範囲指定

B4セルの指定　Range（”B4”）
B6セルの指定 Cells（６,2)

セルの参照方法、なぜ２つあるの？
単独セルはどちらでも指定できるが、範囲指定は、Rangeしかできない！

Cells :　単独セルを指定　　　　　
Range：単独セル・セル範囲を指定

セルの範囲指定をやってみましょう！

セル範囲指定
Range(”起点セル：終点セル”）

この数値の部分を変数に置き換えて、範囲指定することができます

セル範囲指定
Range(起点セル,終点セル）は、

Range(Cells(起点),Cells(終点))と書けるので

これで、プログラムで扱いやすくなりますね！

Rangeを使うと…

Rangeを使うと…

単独セルのコピー　

Range(“B2”).Copy Range(“F2”)

セル情報すべてがコピーされる

本来は、このように書きます
Range(“B2”).Copy Destination:=Range(“F2”)

Destinationは、行き先・宛先・目的地という意味
‘

セル範囲のコピー

Range(“B3:B5”).Copy Range(“F3”)
Destinationは、１つのセルでOK！

形式を指定して貼付け
Range(“B3:D5”).Copy

目的地を書かない場合、Copyしたものはクリップボードに保存されます
点線の点滅箇所がクリップボードに保存されています

コピー内容がクリップボードに保存されている間は、
何度でもこの値を使用することができます

RengeにPasteメソッドはありません
PasteSpecialメソッドを使います

Range(“F7”).PasteSpecial xlPasteValues　：値のみ貼付け
Range(“F11”).PasteSpecial xlPasteFormats：書式のみ貼付け

このPasteSpecialは、EXCELの形式を指定して貼付けの
貼付け項目と同じものを指定できます

コピーモードの解除
Application.CutCopyMode = False

点線の点滅が消えます

Currentregion とは、CTRL＋Shift+＊で選択される範囲

この範囲をアクティブセル領域と言います

アクティブセル領域とは、選択されているセル範囲から、
すべての方向の最初の空白行・空白列までの領域のことです

＊範囲の中に空白行があってはならない

アクティブセル領域
Range(“A1”).CurrentRegion.Select

空白行・空白列で囲まれた領域
指定するセルは、領域内の値のあるセルなら、どのセルでもOK!

終点を書かなくてもいいので、CurrentRegion便利だよね～
CurrentRegionなしに、セル範囲は語れません！
これからも、頻出です

Range(“A” & Rows.Count).End(xlUp).Row

Cells(Rows.Count, １).End(xlUp).Row

Rows.Count：シートの行数

With Range(“B2”).CurrentRegion
　　　　　　MsgBox .Row + .Rows.Count – 1
End With　　　　　　　　　　　　
　　　　　　　　　　　　で表せます　　　　　　　　　　　

Range(“B2”).CurrentRegion.Rows.Count

CurrentRegionの行数

Range(“B2”).CurrentRegion.Row

CurrentRegionの開始行

With Range(“B2”).CurrentRegion
　　　　　　MsgBox .Row + .Rows.Count – 1
End With　　　　　　　　　　　　

With Range(“B2”).CurrentRegion
　　　　MsgBox .Rows(.Rows.Count).Row
End With　　　　　　　　　　　

で表せます

With　Range(“B2”).CurrentRegion
.Rows(2).Select

CurrentRegionの２列目

.Rows.Count

CurrentRegionの行数

.Rows(.Rows.Count).Select

選択した行数は…
MsgBox .Rows(.Rows.Count).Row

https://learn.microsoft.com/

Range.Row プロパティ ：範囲内の最初の領域の最初の行の番号を返します
　　　　　　　　　　　　 取得のみ可能な Long 値です
Range.Rows プロパティ：指定した範囲の行を表す Range オブジェクトを返します

MsgBox Range(“B2”).CurrentRegion.Count

CurrentRegionの要素数

With Range(“B2”)
.CurrentRegion(.CurrentRegion.Count).Select

MsgBox .CurrentRegion(.CurrentRegion.Count).Row
End With

ジャンプ機能

セル選択　　　　　　　　　　　　　　　　　　　最後のセルを選択

最後のセルを選択

Range(“B2”).SpecialCells(xlCellTypeLastCell).Select

最後のセルの行
MsgBox Range(“B2”).SpecialCells(xlLastCell).Row
xlLastCellでもOK！

シートの使用セルをSelect
ActiveSheet.UsedRange.Select

使用セルの最終行のSelect
ActiveSheet.UsedRange.Rows(ActiveSheet.UsedRange.Rows.Count).Select

使用セルの行は…
MsgBox ActiveSheet.UsedRange.Rows(ActiveSheet.UsedRange.Rows.Count).Row

よって、UsedRangeを使ってこの式で求まる

With ActiveSheet.UsedRange
　　　　　　　　MsgBox .Rows(.Rows.Count).Row
　　End With　　　　　　　　　　　

最終行＝開始行+行数-1で求まる

開始行は…
MsgBox “開始行＝” & ActiveSheet.UsedRange.Row

行数は…
MsgBox “行数＝” & ActiveSheet.UsedRange.Rows.Count

With ActiveSheet.UsedRange
　　　　　　MsgBox .Row + .Rows.Count – 1
End With　　　　　　　　　　　　

B2セルを起点に
Range(“B2”).Select
下に２、右に１Offset
ActiveCell.Offset(2, 1).Select
上に２、左に１Offset
ActiveCell.Offset(-2, -1).Select

CurrentRegionを1行下に移動
Range(“B2”).CurrentRegion.Offset(1).Select

CurrentRegionを1列右に移動
Range(“B2”).CurrentRegion.Offset(, 1).Select

CurrentRegionを1行下1列右に移動
Range(“B2”).CurrentRegion.Offset(1, 1).Select

Range(“C3”).Resize(2, 3).Select ‘これと
Range(“C3:D5”).Resize(2, 3).Select ‘これは同じ
終点は関係なし

With Range(“B2”).CurrentRegion

1行縮めて選択　列は省略(そのまま)
.Offset(1).Resize(.Rows.Count – 1).Select

明細のみ選択　1行下１列右に移動して
.Offset(1, 1).Resize(.Rows.Count – 1, .Columns.Count – 1).Select

明細のみ選択できました～

①AutoFilter　Endプロパティで最終行を求めてCopy

With Worksheets(“売上明細”)

売上明細データの２列目の店名をABCストアで抽出する
.Range(“B2″).AutoFilter Field:=2, Criteria1:=”ABCストア”
あるいは
.Range(“B2″).AutoFilter 2,”ABCストア”

オートフィルターの範囲は、範囲でなくても対象のどこかのセルを指定すればOK!
今回はRange(“B2”)

フィルターをかけたデータの最終行？
Gyo = .Cells(Rows.Count, 3).End(xlUp).Row

With .Range(“B2”).CurrentRegion
　　　　　　MsgBox .Rows(.Rows.Count).Row
End With　　　　　　　　　　　　　

最終行は２８？

Endプロパティで求めた最終行は、ABCストアの最終データ行
CurrentRegionで求めた最終行は、データ最終行でOK！

店名と見出しはいらない　D3セルからG列の最終行までコピー
.Range(.Range(“D3”), .Cells(Gyo, 7)).Copy

この範囲をCopy

点線部分がCopy対象？
D3セルからG列の最終行間の他のデータはどうなっているの？

全データを表示すると…

ABCストアのデータのみCopy対象になっている
なので…

Copyした値を店名シートのB５セルに値のみ貼付け
Worksheets(“ABCストア”).Range(“B5”).PasteSpecial Paste:=xlPasteValues

そのままPasteしてOK！
ABCストアの値のみPasteできた
可視セルをCopyできました

オートフィルターの解除
.Range(“B2”).AutoFilter

フィルター対象のデータがない場合

ABCスーパーでフィルター
.Range(“B2”).AutoFilter 2, “ABCスーパー”

２行目からデータが始まっているから

.Range(.Range(“D3”), .Cells(Gyo, 7)).Copy

Worksheets(“ABCストア”).Range(“B5”).PasteSpecial _
Paste:=xlPasteValues

当然こうなるよね！

オートフィルの件数があるかどうか判断する必要がある
データがあるなら
Gyo > 2 の時、Copy→Paste

②AutoFilter　CurrentRegionを使ってCopy

With .Range(“B2”).CurrentRegion


１行下に、２列右にOffset
.Offset(1, 2).Select

さらに、行数は-1、列数は-２、Resize
.Offset(1, 2).Resize(.Rows.Count – 1, .Columns.Count – 2).Select

Resizeしてから、Offsetしても結果は一緒
.Resize(.Rows.Count – 1, .Columns.Count – 2).Offset(1, 2).Copy

With .Range(“B2”).CurrentRegion

どちらかの式でCopy
.Offset(1, 2).Resize(.Rows.Count – 1, .Columns.Count – 2).Copy
.Resize(.Rows.Count – 1, .Columns.Count – 2). Offset(1, 2).Copy

End With

あとは①と同じ

フィルター対象のデータがない場合

ABCスーパーでフィルター
.Range(“B2”).AutoFilter 2, “ABCスーパー”

２行目からデータが始まっているから

貼り付けるとこうなる

全データが貼り付けられている

①と同様にオートフィルの件数があるかどうか判断する必要がある

データがあるなら
Gyo > 2 の時、Copy→Paste

この方は、Excel VBAの初級者を脱したい方向けの動画をアップされています
私の動画は、自分が初心者ですので、初心者向けです(^^)v

この動画は、Offsetプロパティでセルを指定して、変化に強いコードに…という動画ですが、
範囲の指定方法なども、とても参考になると思います

SUMIF関数を使って、店名別に売上合計金額を下のシートに求めます

範囲の中から検索条件に一致するデータの合計を返します

TenmeiRange, KingakuRange に、 オブジェクト変数Rangeを指定する
オブジェクト変数に値を格納するために、Setステートメントを使用する

TenmeiRange ：売上明細シートの .Range(“C3”)～最終行の３列目を指定
KingakuRange ：売上明細シートの .Range(“G3”)～最終行の７列目を指定

SumIf関数はExcelの関数ですので、WorksheetFunctionが必要

検索条件　Cells(i,2)：売上合計シートの店名列…３から７行目
売上合計　Cells(i,3)：TenmeiRangeの中から、検索条件に対応する
KingakuRangeの合計を売上合計３列…３から７行目に表示する

Sub Uriage_Goukei1()
'店名別売上合計

    Dim Gyo As Long, i As Long
    Dim TenmeiRange As Range, KingakuRange  As Range

    With Worksheets("売上明細")
        Gyo = .Cells(Rows.Count, 3).End(xlUp).Row
        '店名の範囲
        Set TenmeiRange = .Range(.Range("c3"), .Cells(Gyo, 3))
        '金額の範囲
        Set KingakuRange = .Range(.Range("g3"), .Cells(Gyo, 7))
    End With
    
    With Worksheets("売上合計")
        For i = 3 To 7
            '検索条件
            Debug.Print .Cells(i, 2)
            '売上合計を求める
            .Cells(i, 3) = WorksheetFunction. _
                SumIf(TenmeiRange, .Cells(i, 2), KingakuRange)
        Next i
    End With

End Sub

短いシンプルなプログラムですよね！
範囲を設定することと、SumIf関数を用いることでで
シンプルなプログラムが実現できているなと感じました～

同じ処理をオフセットを用いて行います
この動画の主旨は、Offsetプロパティでセルを指定して、変化に強いコードに…という内容です

宣言、オブジェクト変数に代入するところは同じです
Worksheets(“売上合計”).Range(“B2”)を起点とします

１から５は、店の数です
起点から、i 行下がったOffset(i)セルを検索対象とします
求めた結果を、その１つ右Offset(i,1)のセルに代入します

このプログラムの優れたところは、この起点さえ、変更すれば、
どの位置にも、この計算結果を書き出すことができます
他のプログラムは、一切変更なしです

Sub Uriage_Goukei2()
'店名別売上合計Offsetを使って

    Dim Gyo As Long, i As Long
    Dim TenmeiRange As Range, KingakuRange As Range
    
    With Worksheets("売上明細")
        Gyo = .Cells(Rows.Count, 3).End(xlUp).Row
        '店名の範囲
        Set TenmeiRange = .Range(.Range("c3"), .Cells(Gyo, 3))
        '金額の範囲
        Set KingakuRange = .Range(.Range("g3"), .Cells(Gyo, 7))
    End With
    
    With Worksheets("売上合計").Range("B2")
        For i = 1 To 5
            
            '売上合計を求める
            .offset(i, 1) = WorksheetFunction. _
                    SumIf(TenmeiRange, .offset(i), KingakuRange)
                    
        Next i
    End With

End Sub