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

how about
cells(rows.count,"a")
or
cells(rows.count,1)

--
Don Guillett
SalesAid Software

"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

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

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

Interesting...

To throw my 2c in about future proofing.
If Microsoft were ever to introduce 65536 rows, I believe it would be per
worksheet/workbook, not application level, for backward compatibility
reasons.
It's possible then that the ActiveSheet and the working sheet could have a
different number of rows.
Code relying on Rows.Count would error because, for example,
Cells(4294967296,1) causes overflow for older worksheets.

That said, I've heard no indication that the row restriction will ever
changed.
Personally, I never use a sheet reference.

Rob


"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

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

Hi,

Thanks for all your comments. I was unable to get back to
you all sooner, because somehow I couldn't reach this site
again until now. Everything was locked up for a while,
for some reason.

Again, thanks.

-----Original 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
.