【マクロ・VBA】行数や列数をカウントする

Sub 選択されている列数を取得()
  MsgBox Selection.Columns.Count
End Sub

Sub GetColumnCount()

    ' 列数を取得
    Dim columnCount As Integer
    columnCount = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column
    
    ' 列数を表示
    MsgBox "列数は " & columnCount & " です。"

End Sub

Sub GetColumnCount()

    ' ワークシートの使用範囲を取得
    Dim usedRange As Range
    Set usedRange = ActiveSheet.UsedRange
    
    ' 列数を取得
    Dim columnCount As Integer
    columnCount = usedRange.Columns.Count
    
    ' 列数を表示
    MsgBox "列数は " & columnCount & " です。"

End Sub

Sub カウント_最終行_最終列()

Dim ws1 As Worksheet
Set ws1 = ThisWorkbook.ActiveSheet
Dim LastRow1 As Long
LastRow1 = ws1.Cells(Rows.Count, 1).End(xlUp).Row
Dim LastCol1 As Long
LastCol1 = ws1.Cells(2, Columns.Count).End(xlToLeft).Column
Dim MyCell As Range
Set MyCell = ws1.Range("A1", Cells(LastRow1, LastCol1))

Dim tmp As Variant
tmp = Split(Cells(1, LastCol1 + LastCol2).Address(True, False), "$")

MsgBox "完了" & vbLf & vbLf & "最終行" & LastRow1 & vbLf & "最終列" & LastCol1 & "＿" & tmp(0)
End Sub

Sub 列のカウント()

Dim cnt1 As Long
Dim cnt2 As Long
Dim cnt3 As Long
Dim cnt4 As Long

Dim myrange As Range
Dim LastRow1 As Long
LastRow1 = ThisWorkbook.ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
Set myrange = ThisWorkbook.ActiveSheet.Range("A2:A" & LastRow1)

cnt1 = WorksheetFunction.Count(myrange)                           '数値のセルの個数
cnt2 = WorksheetFunction.CountA(myrange)                         '入力済みのセルの個数
cnt3 = WorksheetFunction.CountBlank(myrange)                    'ﾌﾞﾗﾝｸｾﾙの個数
cnt4 = WorksheetFunction.Subtotal(9, ThisWorkbook.ActiveSheet.Range("A:A"))

MsgBox "【カウント】" & vbCrLf & vbCrLf & _
"・行数(A2～)                       ： " & LastRow1 - 1 & vbCrLf & _
"・入力済セルの個数(A2～)       ： " & cnt2 & vbCrLf & _
"・ブランクセルの個数（A2～）： " & cnt3 & vbCrLf & _
"・数値セルの個数（A2～）       ： " & cnt1 & vbCrLf & _
"・合計（A2～）                     :　" & cnt4 & vbCrLf

MsgBox "完了"
End Sub

Sub シート作成_別ウィンドウ_カウント()

Dim wb1 As Workbook
    Set wb1 = ThisWorkbook
Dim ws1 As Worksheet
    Set ws1 = wb1.ActiveSheet
Dim LastRow1 As Long
    LastRow1 = ws1.Cells(Rows.Count, 1).End(xlUp).Row
Dim LastCol1 As Long
    LastCol1 = ws1.Cells(1, Columns.Count).End(xlToLeft).Column
Dim MyRange1 As Range
    Set MyRange1 = ws1.Range("A2:A" & LastRow1)

Dim wb2 As Workbook
    Set wb2 = ThisWorkbook
Dim ws2 As Worksheet
    Set ws2 = wb2.ActiveSheet.Next
Dim LastRow2 As Long
    LastRow2 = ws2.Cells(Rows.Count, 2).End(xlUp).Row
Dim LastCol2 As Long
    LastCol2 = ws2.Cells(2, Columns.Count).End(xlToLeft).Column
Dim MyRange2 As Range
    Set MyRange2 = ws2.Range("A2:A" & LastRow2)

Worksheets.Add(After:=ActiveSheet).Name = "カウント" & "_" & VBA.Format(Now(), "h時mm分ss秒")

ThisWorkbook.ActiveSheet.Range("A2") = "行数(A2～)"
ThisWorkbook.ActiveSheet.Range("A3") = "列数"
ThisWorkbook.ActiveSheet.Range("A4") = "入力済セルの個数(A2～)"
ThisWorkbook.ActiveSheet.Range("A5") = "ブランクセルの個数（A2～）"
ThisWorkbook.ActiveSheet.Range("A6") = "数値セルの個数（A2～）"
ThisWorkbook.ActiveSheet.Range("A7") = "合計（A2～）"

ThisWorkbook.ActiveSheet.Range("B1") = "左のシート（アクティブシート）"
ThisWorkbook.ActiveSheet.Range("B2") = LastRow1
ThisWorkbook.ActiveSheet.Range("B3") = LastCol1
ThisWorkbook.ActiveSheet.Range("B4") = WorksheetFunction.CountA(MyRange1)
ThisWorkbook.ActiveSheet.Range("B5") = WorksheetFunction.CountBlank(MyRange1)
ThisWorkbook.ActiveSheet.Range("B6") = WorksheetFunction.Count(MyRange1)
ThisWorkbook.ActiveSheet.Range("B7") = WorksheetFunction.Subtotal(9, ws1.Range("B:B"))

ThisWorkbook.ActiveSheet.Range("C1") = "右のシート"
ThisWorkbook.ActiveSheet.Range("C2") = LastRow2
ThisWorkbook.ActiveSheet.Range("C3") = LastCol2
ThisWorkbook.ActiveSheet.Range("C4") = WorksheetFunction.CountA(MyRange2)
ThisWorkbook.ActiveSheet.Range("C5") = WorksheetFunction.CountBlank(MyRange2)
ThisWorkbook.ActiveSheet.Range("C6") = WorksheetFunction.Count(MyRange2)
ThisWorkbook.ActiveSheet.Range("C7") = WorksheetFunction.Subtotal(9, ws2.Range("B:B"))

With ActiveWorkbook
    .NewWindow
    Windows.Arrange ArrangeStyle:=xlArrangeStyleCascade
    ActiveWindow.WindowState = xlNormal
    ActiveWindow.Width = 300
    ActiveWindow.Height = 500
    ActiveWindow.EnableResize = False
End With

MsgBox "完了"
End Sub