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.


3 comments:

  1. I am happy to find your distinguished way of writing the post. Now you make it easy for me to understand and implement the concept. Thank you for the post.

    Arianne
    www.gofastek.com

    ReplyDelete
  2. There were times that whatever good you wanted to show to a person or people still you can't please them but the only thing you need to do is to keep yourself calm, be professional, open-minded and patient. Everything has a reason,and an end.
    Visit my site for more good vibes. Thank you.

    triciajoy.com

    www.triciajoy.com

    ReplyDelete
  3. Love it! Very interesting topics, I hope the incoming comments and suggestion are equally positive. Thank you for sharing this information that is actually helpful.


    ufgop.org
    ufgop.org

    ReplyDelete