CODEWITHSUNDEEP
excelvba
Xavi
Xavi

Reputation: 207

Select the range from first cell used to last cell used in column

I have a macro which selects my first cell used in column M (variable is Firstrow). This macro detects the last cell used in column M (variable is lastrow).

I would like that my macro selects from first cell used in column M to last cell used in column M.

I tried:

Range (Firsttrow & "M:M" & lastrow)

but I got an error message

“Compile error: invalid use of property”

enter image description here

Sub Selectfromfirstrowusedtolastrowusedused()
Dim lastrow, Firstrow As Long
lastrow = ActiveSheet.Range("M" & Rows.Count).End(xlUp).Row
Firstrow = Range("M1").End(xlDown).Row
Debug.Print Firstrow
Debug.Print lastrow
Range (Firsttrow & "M:M" & lastrow)
End Sub

Upvotes: 0

Views: 2608

Answers (2)

SJR
SJR

Reputation: 23081

You need

Range("M" & Firsttrow & ":M" & lastrow).Select

because it is column then row using Range. An alternative would be

Range(cells(firstrow,"M"),cells(lastrow,"M")).Select

Not that you rarely need to select anything though.

Also, in this line

Dim lastrow, Firstrow As Long

lastrow is declared as a variant so better to do

Dim lastrow As Long, Firstrow As Long

Upvotes: 2

José Teixeira
José Teixeira

Reputation: 31

The right way to do it would be

Range("M" & Firsttrow & ":M" & lastrow")

Upvotes: 1

Related Questions