repeated end(xldown)

[R.VENKATARAMAN]

i have to write
activecell.end(xldown).end(xldown).end(xldown).select

is there easier way of writing this repeated functions.

[Doug Glancy]

I you're trying to select the last non-blank cell in the column then this
would work:

Cells(Rows.Count, ActiveCell.Column).End(xlUp).Select

ht,

Doug


"R.VENKATARAMAN" <vram26@vsnl$$$.net> wrote in message
news:[email protected]...
>i have to write
> activecell.end(xldown).end(xldown).end(xldown).select
>
> is there easier way of writing this repeated functions.
>
>

[R.VENKATARAMAN]

thanks.
that is not the last non blank cell but i have to come down to a particular
cell which needs end(xldonw) 2 or 3 times.
is there something like
<end(xldown)(2)>
if know this gives me some other cell i.e. gives me
end(xldown).offset(1,0)

my data is
1
2
3
(two blank cells)

4
5
6
I want to go to 4
<range("a1").end(xldown).end(xldown).select >gives 4







"Doug Glancy" <[email protected]> wrote in message
news:[email protected]...
> I you're trying to select the last non-blank cell in the column then this
> would work:
>
> Cells(Rows.Count, ActiveCell.Column).End(xlUp).Select
>
> ht,
>
> Doug
>
>
> "R.VENKATARAMAN" <vram26@vsnl$$$.net> wrote in message
> news:[email protected]...
> >i have to write
> > activecell.end(xldown).end(xldown).end(xldown).select
> >
> > is there easier way of writing this repeated functions.
> >
> >
>
>

[Doug Glancy]

Is it always the next cell after the first two blank cells, but the row can
be different? If so, I think your solution is as good as any. You could
also do something like:

Range("A1").End(xlDown).Offset(3).Select

but it looks like you've thought of something like that. I need a better
description of the pattern.

hth,

Doug


"R.VENKATARAMAN" <vram26@vsnl$$$.net> wrote in message
news:%[email protected]...
> thanks.
> that is not the last non blank cell but i have to come down to a
> particular
> cell which needs end(xldonw) 2 or 3 times.
> is there something like
> <end(xldown)(2)>
> if know this gives me some other cell i.e. gives me
> end(xldown).offset(1,0)
>
> my data is
> 1
> 2
> 3
> (two blank cells)
>
> 4
> 5
> 6
> I want to go to 4
> <range("a1").end(xldown).end(xldown).select >gives 4
>
>
>
>
>
>
>
> "Doug Glancy" <[email protected]> wrote in message
> news:[email protected]...
>> I you're trying to select the last non-blank cell in the column then this
>> would work:
>>
>> Cells(Rows.Count, ActiveCell.Column).End(xlUp).Select
>>
>> ht,
>>
>> Doug
>>
>>
>> "R.VENKATARAMAN" <vram26@vsnl$$$.net> wrote in message
>> news:[email protected]...
>> >i have to write
>> > activecell.end(xldown).end(xldown).end(xldown).select
>> >
>> > is there easier way of writing this repeated functions.
>> >
>> >
>>
>>
>
>

[[email protected]]

thanks for your patience.
the number of blank cells is variable after first set of non blank
cells.




Doug Glancy wrote:
> Is it always the next cell after the first two blank cells, but the row can
> be different? If so, I think your solution is as good as any. You could
> also do something like:
>
> Range("A1").End(xlDown).Offset(3).Select
>
> but it looks like you've thought of something like that. I need a better
> description of the pattern.
>
> hth,
>
> Doug
>
>
> "R.VENKATARAMAN" <vram26@vsnl$$$.net> wrote in message
> news:%[email protected]...
> > thanks.
> > that is not the last non blank cell but i have to come down to a
> > particular
> > cell which needs end(xldonw) 2 or 3 times.
> > is there something like
> > <end(xldown)(2)>
> > if know this gives me some other cell i.e. gives me
> > end(xldown).offset(1,0)
> >
> > my data is
> > 1
> > 2
> > 3
> > (two blank cells)
> >
> > 4
> > 5
> > 6
> > I want to go to 4
> > <range("a1").end(xldown).end(xldown).select >gives 4
> >
> >
> >
> >
> >
> >
> >
> > "Doug Glancy" <[email protected]> wrote in message
> > news:[email protected]...
> >> I you're trying to select the last non-blank cell in the column then this
> >> would work:
> >>
> >> Cells(Rows.Count, ActiveCell.Column).End(xlUp).Select
> >>
> >> ht,
> >>
> >> Doug
> >>
> >>
> >> "R.VENKATARAMAN" <vram26@vsnl$$$.net> wrote in message
> >> news:[email protected]...
> >> >i have to write
> >> > activecell.end(xldown).end(xldown).end(xldown).select
> >> >
> >> > is there easier way of writing this repeated functions.
> >> >
> >> >
> >>
> >>
> >
> >

[Dave Peterson]

Maybe you could just loop the number of times you need:

dim HowMany as long
dim iCtr as long
dim DestCell as Range

howmany = 3

set destcell = range("a1")

for ictr = 1 to howmany
set destcell = destcell.end(xldown)
next ictr

msgbox destcell.address

[email protected] wrote:
>
> thanks for your patience.
> the number of blank cells is variable after first set of non blank
> cells.
>
> Doug Glancy wrote:
> > Is it always the next cell after the first two blank cells, but the row can
> > be different? If so, I think your solution is as good as any. You could
> > also do something like:
> >
> > Range("A1").End(xlDown).Offset(3).Select
> >
> > but it looks like you've thought of something like that. I need a better
> > description of the pattern.
> >
> > hth,
> >
> > Doug
> >
> >
> > "R.VENKATARAMAN" <vram26@vsnl$$$.net> wrote in message
> > news:%[email protected]...
> > > thanks.
> > > that is not the last non blank cell but i have to come down to a
> > > particular
> > > cell which needs end(xldonw) 2 or 3 times.
> > > is there something like
> > > <end(xldown)(2)>
> > > if know this gives me some other cell i.e. gives me
> > > end(xldown).offset(1,0)
> > >
> > > my data is
> > > 1
> > > 2
> > > 3
> > > (two blank cells)
> > >
> > > 4
> > > 5
> > > 6
> > > I want to go to 4
> > > <range("a1").end(xldown).end(xldown).select >gives 4
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > > "Doug Glancy" <[email protected]> wrote in message
> > > news:[email protected]...
> > >> I you're trying to select the last non-blank cell in the column then this
> > >> would work:
> > >>
> > >> Cells(Rows.Count, ActiveCell.Column).End(xlUp).Select
> > >>
> > >> ht,
> > >>
> > >> Doug
> > >>
> > >>
> > >> "R.VENKATARAMAN" <vram26@vsnl$$$.net> wrote in message
> > >> news:[email protected]...
> > >> >i have to write
> > >> > activecell.end(xldown).end(xldown).end(xldown).select
> > >> >
> > >> > is there easier way of writing this repeated functions.
> > >> >
> > >> >
> > >>
> > >>
> > >
> > >

--

Dave Peterson

[Doug Glancy]

You could wrap what Dave is suggesting in a function:

Sub test()
repeated_downs(ActiveCell, 3).Select
End Sub

Function repeated_downs(startcell As Range, howmany As Long) As Range
Dim destcell As Range
Dim ictr As Long

Set destcell = startcell
For ictr = 1 To howmany
Set destcell = destcell.End(xlDown)
Next ictr
Set repeated_downs = destcell
End Function

hth,

Doug

"Dave Peterson" <[email protected]> wrote in message
news:[email protected]...
> Maybe you could just loop the number of times you need:
>
> dim HowMany as long
> dim iCtr as long
> dim DestCell as Range
>
> howmany = 3
>
> set destcell = range("a1")
>
> for ictr = 1 to howmany
> set destcell = destcell.end(xldown)
> next ictr
>
> msgbox destcell.address
>
> [email protected] wrote:
> >
> > thanks for your patience.
> > the number of blank cells is variable after first set of non blank
> > cells.
> >
> > Doug Glancy wrote:
> > > Is it always the next cell after the first two blank cells, but the
row can
> > > be different? If so, I think your solution is as good as any. You
could
> > > also do something like:
> > >
> > > Range("A1").End(xlDown).Offset(3).Select
> > >
> > > but it looks like you've thought of something like that. I need a
better
> > > description of the pattern.
> > >
> > > hth,
> > >
> > > Doug
> > >
> > >
> > > "R.VENKATARAMAN" <vram26@vsnl$$$.net> wrote in message
> > > news:%[email protected]...
> > > > thanks.
> > > > that is not the last non blank cell but i have to come down to a
> > > > particular
> > > > cell which needs end(xldonw) 2 or 3 times.
> > > > is there something like
> > > > <end(xldown)(2)>
> > > > if know this gives me some other cell i.e. gives me
> > > > end(xldown).offset(1,0)
> > > >
> > > > my data is
> > > > 1
> > > > 2
> > > > 3
> > > > (two blank cells)
> > > >
> > > > 4
> > > > 5
> > > > 6
> > > > I want to go to 4
> > > > <range("a1").end(xldown).end(xldown).select >gives 4
> > > >
> > > >
> > > >
> > > >
> > > >
> > > >
> > > >
> > > > "Doug Glancy" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > >> I you're trying to select the last non-blank cell in the column
then this
> > > >> would work:
> > > >>
> > > >> Cells(Rows.Count, ActiveCell.Column).End(xlUp).Select
> > > >>
> > > >> ht,
> > > >>
> > > >> Doug
> > > >>
> > > >>
> > > >> "R.VENKATARAMAN" <vram26@vsnl$$$.net> wrote in message
> > > >> news:[email protected]...
> > > >> >i have to write
> > > >> > activecell.end(xldown).end(xldown).end(xldown).select
> > > >> >
> > > >> > is there easier way of writing this repeated functions.
> > > >> >
> > > >> >
> > > >>
> > > >>
> > > >
> > > >
>
> --
>
> Dave Peterson