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

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"BigH" <[email protected]> wrote in message
news:[email protected]...
> Hi there,
>
> Is it possible using code to find the bottom of a range in say column A
>
> tia BigH
>
>

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" <[email protected]> wrote in message
news:[email protected]...
> Hi there,
>
> Is it possible using code to find the bottom of a range in say column A
>
> tia BigH
>
>

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" <[email protected]> wrote in message
> news:[email protected]...
> > Hi there,
> >
> > Is it possible using code to find the bottom of a range in say column A
> >
> > tia BigH
> >
> >

--

Dave Peterson

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

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

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" <[email protected]> wrote in message
news:[email protected]...
> 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

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