Another point

Neither approach works if there is data in the last cell.


"Bob Phillips" wrote in message
...
Rick,

A little background.

As John shows, you don't need any sheet reference, as Rows.Count will be a
constant value contained within Excel, the correct value dependent upon
the
Excel version.So although you can use
S1.Rows.Count
it is just not necessary.

As well as future proofing, and past proofing your code, it's efficient as
it is using a pre-defined constant.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"John Wilson" wrote in message
...
Rick,

S1.Range("D" & Rows.Count).End(xlUp).Select

John

"Rick" wrote in message
...
Hi,

Bob Phillips had brought up something interesting the
other day with a little humor. But I can't remember the
exact code.

With this code, S1.Range("D65536").End(xlUp).Select, how
can it be rewritten to use Rows.Count instead of 65536, as
suggested by Bob? I always don't have enough or too many
quotation marks when I try to put these kinds of things
together with the ampersands. For some reason, I'm
stuck. I usually figure it out, but I must be doing
something wrong.

The objective is to use Rows.Count in the event that
Microsoft changes the number of rows in Excel...seems like
a good exercise this morning as well. :)

This could be rewritten from this:

Dim S1 As Worksheet
Set S1 = Sheets(1)
S1.Range("D65536").End(xlUp).Select

to something like:

S1.Range(" & "D" & S1.Rows.Count & ").End(xlUp).Select

But with the correct number of quotation marks and
configuration to make it function properly.

Thanks, Rick