Monday, June 4, 2012

Excel VBA – Get the Size of a Range


Sometimes the size of the selected range is determined dynamically. I need to check the number of rows and columns in the selected range. The following macro can do the work.

 Sub SizeOfRange()

    Dim Rng As Range

    Dim RowCount, ColCount As Integer

    '-----The size of range is determined dynamically.

    Set Rng = ActiveSheet.Range(Cells(6, 3), Cells(Cells(6, _ 3).End(xlDown).Row, Cells(6, 3).End(xlToRight).Column))

    '---- The number of rows and columns of the range

    RowCount = Rng.Rows.Count

    ColCount = Rng.Columns.Count

    MsgBox "The number of row is " & RowCount

    MsgBox "The number of Column is " & ColCount

End Sub



© Copyright Exceltipsandkeys All Rights Reserved.


No comments:

Post a Comment