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

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

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

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

--

Dave Peterson

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