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]

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]

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

 

[venkat1926]

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

 

[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

 

[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