Excel VBA - Summary table fori Vacant and Occupied

Sub test()
    Dim a, i As Long, ii As Long, n As Long, w, flg As Boolean
    a = Cells(1).CurrentRegion.Value
    With [e3].CurrentRegion.Offset(1)
        .ClearContents: .Borders.LineStyle = xlNone
        With CreateObject("System.Collections.ArrayList")
            For i = 2 To UBound(a, 1)
                If a(i, 3) <> "" Then
                    ReDim w(5): n = n + 1: w(0) = n: flg = False
                    w(1) = "Group" & n: w(4) = Trim$(Split(a(i, 3))(1))
                    For ii = 0 To Val(Split(a(i, 3))(1)) - 1
                        If w(2) = "" Then w(2) = a(i, 2)
                        w(3) = a(i + ii, 2)
                    Next
                    .Add w: i = i + ii - 1
                Else
                    If Not flg Then
                        n = n + 1: ReDim w(5): w(0) = n
                        w(1) = "Group" & n: w(5) = 1
                        w(2) = a(i, 2): w(3) = a(i, 2)
                        .Add w: flg = True
                    Else
                        w = .Item(.Count - 1)
                        w(3) = a(i, 2): w(5) = w(5) + 1
                        .Item(.Count - 1) = w
                    End If
                End If
            Next
            a = Application.Index(.ToArray, 0, 0)
        End With
        With .Resize(n, 6)
            .Value = a: .Borders.Weight = 2
        End With
    End With
End Sub