Finding the last row in or length of a passed range

[JenniferMurphy]

Given pratings, a range, I can find the absolute range address, the number of rows and columns, and the number of the first row and column.

?pratings.address
$D$5:$G$14

?pratings.rows.count
 10 

?pratings.columns.count
 4 

?pratings.row
 5 

?pratings.column
 4

Are there expressions that will return the number of the last row (14) and the letter or number of the last column (G or 7) in the range? I know I can extract that information from the results above, but if there is a built-in expression, I would prefer to use it.

Thanks

 

[sadboy309]

With:

[COLOR=#333333]?pratings.address
[/COLOR][COLOR=#333333]$D$5:$G$14[/COLOR]

If cell [G14] have data:

?[COLOR=#333333]pratings[/COLOR].SpecialCells(xlCellTypeLastCell).Address(0,0)
G14
?[COLOR=#333333]pratings[/COLOR].SpecialCells(xlCellTypeLastCell).Column
7
?[COLOR=#333333]pratings[/COLOR].SpecialCells(xlCellTypeLastCell).Row
14

If cell [G14] have not data:

[COLOR=#333333]?pratings.address
[/COLOR][COLOR=#333333]$D$5:$G$14[/COLOR][COLOR=#333333]
[/COLOR]?Evaluate("=Mid(""" & pratings[COLOR=#333333].address[/COLOR] & """, Find(""#"", Substitute(""" & pratings[COLOR=#333333].address[/COLOR] & """, ""$"", ""#"", 3), 1) + 1, Find(""#"", Substitute(""" & pratings[COLOR=#333333].address[/COLOR] & """, ""$"", ""#"", 4), 1) - Find(""#"", Substitute(""" & pratings[COLOR=#333333].address[/COLOR] & """, ""$"", ""#"", 3), 1)-1)")
G

'OR
?Evaluate("=Mid(""" & pratings[COLOR=#333333].address[/COLOR] & """, Find("":"", """ & pratings[COLOR=#333333].address[/COLOR] & """, 1) + 1, Len(""" & pratings[COLOR=#333333].address[/COLOR] & """) - Find("":"", """ & pratings[COLOR=#333333].address[/COLOR] & """, 1))")
$G$14
?Range(Evaluate("=Mid(""" & pratings[COLOR=#333333].address[/COLOR] & """, Find("":"", """ & pratings[COLOR=#333333].address[/COLOR] & """, 1) + 1, Len(""" & pratings[COLOR=#333333].address[/COLOR] & """) - Find("":"", """ & pratings[COLOR=#333333].address[/COLOR] & """, 1))")).Column
7
?Range(Evaluate("=Mid(""" & pratings[COLOR=#333333].address[/COLOR] & """, Find("":"", """ & pratings[COLOR=#333333].address[/COLOR] & """, 1) + 1, Len(""" & pratings[COLOR=#333333].address[/COLOR] & """) - Find("":"", """ & pratings[COLOR=#333333].address[/COLOR] & """, 1))")).Row
14

 

[DanteAmor]

Some options

    fisrt = pratings.Cells(1, 1).Address
    last = pratings.Cells(pratings.Rows.Count, pratings.Columns.Count).Address

 

[Rick Rothstein]

Some options

    fisrt = pratings.Cells(1, 1).Address
    last = pratings.Cells(pratings.Rows.Count, pratings.Columns.Count).Address

You can do this without the Cells object call...

first = pratings(1).Address

last = Pratings(Pratings.Count).Address

 

[DanteAmor]

You can do this without the Cells object call...

first = pratings(1).Address

last = Pratings(Pratings.Count).Address

Thanks Rick for your contributions.


Then we would have:

    Set last = pratings(pratings.Count)
    
    lastcel = last.Address
    lastrow = last.Row
    lastcol = last.Column

 

[JenniferMurphy]

If cell [G14] have data:

. . .

Thanks for taking the time to post all that. But there ought to be a solution that does not depend on the contents of the cells.

 

[JenniferMurphy]

Some options

    fisrt = pratings.Cells(1, 1).Address
    last = pratings.Cells(pratings.Rows.Count, pratings.Columns.Count).Address

Thanks. I even think I can understand that solution.

 

[JenniferMurphy]

You can do this without the Cells object call...

first = pratings(1).Address

last = Pratings(Pratings.Count).Address

Excellent. I'll have to study this solution and compare with Dante's. I think I can learn a lot from that.

Thanks

 

[DanteAmor]

Thanks. I even think I can understand that solution.

Review options in post #5

 

[Fluff]

Another way

   Debug.Print "column", Split(pratings.Address, "$")(3)
   Debug.Print "Row", Split(pratings.Address, "$")(4)