クラスの利用方法｜Excel VBA（番外編）| 現役エンジニア&プログラミングスクール講師

Sub dataClear()

    Dim sheetName(7) As String
    sheetName(1) = "現金"
    sheetName(2) = "カード売上"
    sheetName(3) = "銀行預金"
    sheetName(4) = "日別統合"
    sheetName(5) = "シート統合"
    sheetName(6) = "勤務休憩シート"
    sheetName(7) = "ドリンクバック集計"
    
    Dim tableRow As Integer
    Dim tableColumns As Integer
    Dim i As Integer
    
    For i = 1 To 7
    
        Sheets(sheetName(i)).Select
        
        If i <= 5 Then
            Range("A5").Select
            If ActiveCell.Value <> "" Then
                ActiveCell.CurrentRegion.Select
                tableRow = Selection.Rows.Count
                tableColumns = Selection.Columns.Count
                ActiveCell.Offset(2, 0).Resize(tableRow - 2, tableColumns).Select
                Selection.ClearContents
            End If
        Else
            Range("A2").Select
            If ActiveCell.Value <> "" Then
                ActiveCell.CurrentRegion.Select
                tableRow = Selection.Rows.Count
                tableColumns = Selection.Columns.Count
                ActiveCell.Offset(1, 0).Resize(tableRow - 1, tableColumns).Select
                Selection.ClearContents
            End If
        End If
        
    Next i
    
End Sub

Option Explicit

Private this_tableRow As Integer
Private this_tableColumns As Integer
Private this_rowNum As Integer
Private this_cellAddress As String

Private Sub Class_Initialize()
    this_rowNum = 2
    this_cellAddress = "A5"
    tableRow = tableRow
End Sub

Sub clearMethod()

    Range(this_cellAddress).Select
    If ActiveCell.value <> "" Then
        ActiveCell.CurrentRegion.Select
        this_tableRow = Selection.Rows.Count
        this_tableColumns = Selection.Columns.Count
        ActiveCell.Offset(this_rowNum, 0).Resize(this_tableRow - this_rowNum, this_tableColumns).Select
        Selection.ClearContents
    End If
    
End Sub

Public Property Get tableRow() As String
    tableRow = this_tableRow
End Property

Public Property Let tableRow(ByVal value As String)
    this_tableRow = value
End Property

Public Property Get tableColumns() As String
    tableColumns = this_tableColumns
End Property

Public Property Let tableColumns(ByVal value As String)
    this_tableColumns = value
End Property

Public Property Get rowNum() As String
    rowNum = this_rowNum
End Property

Public Property Let rowNum(ByVal value As String)
    this_rowNum = value
End Property

Public Property Get cellAddress() As String
    cellAddress = this_cellAddress
End Property

Public Property Let cellAddress(ByVal value As String)
    this_cellAddress = value
End Property

Sub dataClear()

    Dim sheetName(7) As String
    sheetName(1) = "現金"
    sheetName(2) = "カード売上"
    sheetName(3) = "銀行預金"
    sheetName(4) = "日別統合"
    sheetName(5) = "シート統合"
    sheetName(6) = "勤務休憩シート"
    sheetName(7) = "ドリンクバック集計"
    
    Dim tableRow As Integer
    Dim tableColumns As Integer
    Dim i As Integer
    
    Dim clearClass As Class1
    Set clearClass = New Class1
    
    For i = 1 To 7
    
        Sheets(sheetName(i)).Select
        
        If i <= 5 Then
            clearClass.clearMethod
        Else
            clearClass.cellAddress = "A2"
            clearClass.rowNum = 1
            clearClass.clearMethod
        End If
        
    Next i
    
    Set clearClass = Nothing
    
End Sub