ExcelBanter - View Single Post

Nigel[_2_]

Darn it must try harder LOL

--

Regards,
Nigel

"Rick Rothstein (MVP - VB)" wrote in
message ...
I like one-liners more than most (I'm sort of "famous" for them over in the
compiled VB newsgroups), but there is too much repeating in your one-liner
for my tastes (in addition to the fact that your one-liner requires one to
type in some 26 characters more than the three-liner I posted).

Rick

"Nigel" wrote in message
...
Compactness!! Now down to 1 lines :)

Sub Rename()
Activesheet.Name = ActiveSheet.Cells(Activesheet.Rows.Count,
"A").End(xlUp) & Format(Activesheet.Cells(Activesheet.Rows.Count,
"E").End(xlUp), "m-d-yy h-mmAM/PM")
End Sub
--

Regards,
Nigel

"SteveM" wrote in message
...
On Jan 6, 6:49 am, "Nigel" wrote:
Doesn't this assume that the last row in A and E are the same? The OP
asked
for "last used cell with a value
in column A and E", which I read as - could be different?

See my solution that takes both value separately, however I should have
used
the format function which would have been better!

--

Regards,
Nigel

"Rick Rothstein (MVP - VB)" wrote in
. ..

Here's something more compact:

Sub SheetRename()
Dim aPart As String, ePart As String, shtName As String

Range("A1").EntireColumn.Cells(Rows.Count, 1).Select
Selection.End(xlUp).Select
aPart = Selection
ePart = Selection.Offset(0, 4)
shtName = aPart & " " & Format(ePart, "m-d-yy h-mmAM/PM")
ActiveSheet.Name= shtName

End Sub

If compact is your goal, what about this for the body of your
SheetRename
subroutine?

Dim R As Range
Set R = ActiveSheet.Cells(Rows.Count, 1).End(xlUp)
R.Parent.Name= R.Value & Format(R.Offset(0, 4).Value, " m-d-yy
h-mmAM/PM")

By the way, I used R.Parent.Nameto keep the last statement from
word-wrapping in newsreaders... it should be replaced by
ActiveSheet.Name
for clarity.

Rick

Attn: Rick Rothstein and his solution.

Touche' on "compactness"

SteveM

P.S. And to think that I thought that it was the "C" guys who were
like that :)