Get length of dynamic range VBA

Question

I need to store the length of a dynamic range based on an initial cell selection and then selecting down to the end of the column. Here' where I am at now

Sub Macro1()

Dim number_prop_components As Integer
Dim range_prop As Range

Range("C26").Select
Set range_prop = Range(Selection, Selection.End(xlDown)).Select
number_prop_components = range_prop.Height
MsgBox (range_prop.Height)

End Sub

When I run the code and get to the line:

Set range_prop = Range(Selection, Selection.End(xlDown)).Select

I get the error:

Run-time error '424': Object required

How can I store a dynamic range and then get it's height or width stored as an integer?

Answer 1

You can't make a range variable = a select, you probably want just the range:

Set range_prop = Range(Selection, Selection.End(xlDown))

You can then use .rows.count and .columns.count against the variable to get the size: range_prop.rows.count

Are you sure you want to use .end(xldown)? it will only go to the first blank, not the whole way if there is a blank.

I have cleaned up your code a little for you here:

Sub Macro1()
    Dim number_prop_components As Long, range_prop As Range
    Set range_prop = Range(Range("C26"), Range("C26").End(xlDown))
    number_prop_components = range_prop.Rows.Count
    MsgBox (number_prop_components)
End Sub

Answer 2

Set range_prop = Range(Selection, Selection.End(xlDown)).Select
number_prop_components = range_prop.Height
MsgBox (range_prop.Height)

Should be:

Set range_prop = Range(Selection, Selection.End(xlDown))
number_prop_components = range_prop.Rows.Count
range_prop.Select
MsgBox number_prop_components

1- When you add .Select at the end, you are calling a subroutine returning nothing, so you can't assign the result.

2- The height of a range is obtained by its number of rows, using myRange.Rows.count

3- dont put parentheses around subroutine parameters, but only when calling a function returning something.

4- Generally avoid working with the selection in VBA. Read How to avoid using Select in Excel VBA macros