Finding the End of a Range
 

Hi there,

Is it possible using code to find the bottom of a range in say column A

tia BigH

Finding the End of a Range
 

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"BigH" wrote in message
...
Hi there,

Is it possible using code to find the bottom of a range in say column A

tia BigH

Finding the End of a Range
 

Sure

LastCell = Range("A1").End(xlDown)

assuminmg there are no embedded blank cells

LastCell = Range("A" & Rows.Count).End(xlUp)

if there might be

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"BigH" wrote in message
...
Hi there,

Is it possible using code to find the bottom of a range in say column A

tia BigH

Finding the End of a Range
 

I bet Bob meant:

Dim LastCell as Range
set LastCell = Range("A1").End(xlDown)
'assuminmg there are no embedded blank cells
set LastCell = Range("A" & Rows.Count).End(xlUp)

Bob Phillips wrote:

Sure

LastCell = Range("A1").End(xlDown)

assuminmg there are no embedded blank cells

LastCell = Range("A" & Rows.Count).End(xlUp)

if there might be

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"BigH" wrote in message
...
Hi there,

Is it possible using code to find the bottom of a range in say column A

tia BigH



--

Dave Peterson

Finding the End of a Range
 

Bob's example will find the end of range provided, as he pointed out,
there are are no blank lines with in the range. This is a good approach
if you know where your range starts.

Dave's example is commonly used to find the last cell within in a
column. This is not the same as Bob's example as you can have multiple
ranges within a column with each separated by one or more blank lines.


Sincerely,
Leith Ross


--
Leith Ross
------------------------------------------------------------------------
Leith Ross's Profile: http://www.excelforum.com/member.php...o&userid=18465
View this thread: http://www.excelforum.com/showthread...hreadid=514205

Finding the End of a Range
 

here is another alternative
Sub check()
Dim here As Range
Dim lastcell As Range
Set here = Selection.CurrentRegion
Set lastcell = Cells(here.Row + here.Rows.Count - 1, here.Column +
here.Columns.Count - 1)

lastcell.Select

End Sub

"BigH" wrote:

Hi there,

Is it possible using code to find the bottom of a range in say column A

tia BigH

Finding the End of a Range
 

Actually, the only difference between Bob's original code and my changes is that
I added the Dim statement. And I added Set to each of the assignment lines.

Bob included both options--he just had a typo and forgot to use Set.

Leith Ross wrote:

Bob's example will find the end of range provided, as he pointed out,
there are are no blank lines with in the range. This is a good approach
if you know where your range starts.

Dave's example is commonly used to find the last cell within in a
column. This is not the same as Bob's example as you can have multiple
ranges within a column with each separated by one or more blank lines.

Sincerely,
Leith Ross

--
Leith Ross
------------------------------------------------------------------------
Leith Ross's Profile: http://www.excelforum.com/member.php...o&userid=18465
View this thread: http://www.excelforum.com/showthread...hreadid=514205

--

Dave Peterson

Finding the End of a Range
 

This is weird, I cannot see my post in here, nor Leith's. I can see Dave's
and Patrick's, and the OP, but no more. See it all fin in Google. Odd!

Bob

"Dave Peterson" wrote in message
...
Actually, the only difference between Bob's original code and my changes
is that
I added the Dim statement. And I added Set to each of the assignment
lines.

Bob included both options--he just had a typo and forgot to use Set.

Leith Ross wrote:

Bob's example will find the end of range provided, as he pointed out,
there are are no blank lines with in the range. This is a good approach
if you know where your range starts.

Dave's example is commonly used to find the last cell within in a
column. This is not the same as Bob's example as you can have multiple
ranges within a column with each separated by one or more blank lines.

Sincerely,
Leith Ross

--
Leith Ross
------------------------------------------------------------------------
Leith Ross's Profile:
http://www.excelforum.com/member.php...o&userid=18465
View this thread:
http://www.excelforum.com/showthread...hreadid=514205

--

Dave Peterson

Finding the End of a Range
 

Netscape 4.78. Still a fine newsreader <vbg.

Bob Phillips wrote:

This is weird, I cannot see my post in here, nor Leith's. I can see Dave's
and Patrick's, and the OP, but no more. See it all fin in Google. Odd!

Bob

"Dave Peterson" wrote in message
...
Actually, the only difference between Bob's original code and my changes
is that
I added the Dim statement. And I added Set to each of the assignment
lines.

Bob included both options--he just had a typo and forgot to use Set.

Leith Ross wrote:

Bob's example will find the end of range provided, as he pointed out,
there are are no blank lines with in the range. This is a good approach
if you know where your range starts.

Dave's example is commonly used to find the last cell within in a
column. This is not the same as Bob's example as you can have multiple
ranges within a column with each separated by one or more blank lines.

Sincerely,
Leith Ross

--
Leith Ross
------------------------------------------------------------------------
Leith Ross's Profile:
http://www.excelforum.com/member.php...o&userid=18465
View this thread:
http://www.excelforum.com/showthread...hreadid=514205

--

Dave Peterson

--

Dave Peterson

Finding the End of a Range
 

I've got IE, and Firefox, I don't need a third. I suppose I could try
Thunderbird, but I couldn't work out how to flag threads on that.

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"Dave Peterson" wrote in message
...
Netscape 4.78. Still a fine newsreader <vbg.

Bob Phillips wrote:

This is weird, I cannot see my post in here, nor Leith's. I can see
Dave's
and Patrick's, and the OP, but no more. See it all fin in Google. Odd!

Bob

"Dave Peterson" wrote in message
...
Actually, the only difference between Bob's original code and my
changes
is that
I added the Dim statement. And I added Set to each of the assignment
lines.

Bob included both options--he just had a typo and forgot to use Set.

Leith Ross wrote:

Bob's example will find the end of range provided, as he pointed
out,
there are are no blank lines with in the range. This is a good
approach
if you know where your range starts.

Dave's example is commonly used to find the last cell within in a
column. This is not the same as Bob's example as you can have
multiple
ranges within a column with each separated by one or more blank
lines.

Sincerely,
Leith Ross

--
Leith Ross

------------------------------------------------------------------------
Leith Ross's Profile:
http://www.excelforum.com/member.php...o&userid=18465
View this thread:
http://www.excelforum.com/showthread...hreadid=514205

--

Dave Peterson

--

Dave Peterson