Hello ,
Can anyone tell me the most efficient code for selecting or referring to the last used row within a named range? I suppose you cannoot use UsedRange within a named range, can you ?
I would also be happy with a good site to turn to when it comes to code for selecting ranges in general.
Thanks a lot
herman
That brings it closer, Zack, but still : I'd like to keep the reference restricted to the NAME of the range in order to keep the possibility
to insert new columns to the left of Column C.
HelloZack,
Yes ; if Range("BlaBla") refers tot range(C:G) and say Column F has the last used row, e.g. 10, I want to select range ("C10:F10"), referring with "BlaBla".
Thanks
Replace the msgbox line with this line...
Range("C" & lRow & ":F" & lRow).Select
Richie, I couldn't get your code to work if the named range was out from col A, at least not accurately. Were you assuming the range started in A1? 7th Tutorial on PSpice::
At that time we only wanted one frequency, so which range type we used didnt matter much. of PSpice uses the file name pspiceev.ini to specify the same
http://dave.uta.edu/dillon/pspice/pspice07.htmHOME |
Here ya go, this will find the last row of the specified named range. Substitute "blah" for whatever your named range is.
Sub nameRngRow()
Dim w As String, x As String, y Developers Guide to the Excel 2007 Range Object::
like define the range with a friendly name, use it in formulas, and The Delete method of the Names collection can be used to delete a named range.
http://msdn.microsoft.com/en-us/library/bb978779.aspxHOME |
Libp2p - Phoenix Labs Wiki::
is used as a lightweight optimized list to test IPs and ranges against. 8 BOM will be written before the list and range names will be encoded in UTF-8.
http://wiki.phoenixlabs.org/wiki/Libp2pHOME |
As String, z As String, a As Integer, b As Integer
w = Range("blah4").Address
x = Right(w, Len(w) - 1)
a = WorksheetFunction.Find(":", x, 1)
y = Right(x, Len(x) - a - 1)
b = WorksheetFunction.Find("$", y, 1)
z = Right(y, Len(y) - b)
MsgBox z
End Sub
I know it's messy, but it handles from single digits to 65536 - tested ok.
Hi Herman,
Is this the sort of thing that you had in mind?Sub Test()
Dim rngTest As Range, lRow As Long
Set rngTest = Range("blah4")
lRow = rngTest.Find(What:="*", After:=rngTest(1, 1), SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row
MsgBox lRow
End SubHTH
EDIT : The UsedRange property applies to worksheets.
Ah! That would be it! I tested with the start range in various places besides starting w/ A1. Makes sense now. :)
Does it matter what column?
Hi Zack,
Didn't notice any problems, although testing was limited (not all ranges started at A1 though).
Hi Herman,
A slightly re-worked response based upon your further clarification of the objective.Sub Test()
Dim rngTest As Range, rngLast As Range
Dim rngLUR As Range
Set rngTest = Range("blah4")
Set rngLast = LastCell(rngTest)
If Not rngLast Is Nothing Then
With rngLast.Parent
Set rngLUR = Range(.Cells(rngLast.Row, rngTest.Column), _
.Cells(rngLast.Row, rngLast.Column))
End With
MsgBox "Last used row in named range is" & vbNewLine & rngLUR.Address
End If
End Sub
Function LastCell(rngToSearch As Range) As Range
Dim iCol As Integer, lRow As Long
If WorksheetFunction.CountA(rngToSearch) > 0 Then
lRow = rngToSearch.Find(What:="*", After:=rngToSearch(1, 1), _
SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
iCol = rngToSearch.Find(What:="*", After:=rngToSearch(1, 1), _
SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
Set LastCell = rngToSearch.Parent.Cells(lRow, iCol)
End If
End FunctionHTH
thanks Richie, that works fine.
But suppose Range("Blah4") = Columns("C:G")
I want to select the last USED row (i.e. 5 cells)
Thanks again
Herman
 Traditional University or MLM University? You Choose
 15 Questions to Ask Your Software Vendor
|
used range in named range