unknown column length
 

Stating an absolute range can sometimes not be possible due to unknowns.
Using (F:F) for instance can be a useful alternative as it looks at the
entire column, not just part of it.
However, is it possible to specify a start cell to search down from?
i.e.(F$10:F) This q is for both formulae and vb.
Thanks,
--
Traa Dy Liooar

Jock

unknown column length
 

You should check out chip pearson website. He has a lot of good
methods

'Excel Redirect' (http://www.cpearson.com)


You can find a range and set a variable to the range. The END method
in VBA works the same has the hot keys Shift-Cntl-down Arrow

If you don't have any blank cells

LastRow = Range("F10").end(xldown).row
Set MyRange = Range("F10:F" & LastRow)


If you have blank cells

LastRow = Range("F" & rows.Count).end(xlup).row
Set MyRange = Range("F10:F" & LastRow)


--
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?userid=229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=145584

unknown column length
 

rng = Cells(Rows.Count, "F").End(xlUp).Row

with Range("F10:F" & rng)
.......
end with



On Oct 19, 4:55*pm, Jock wrote:
Stating an absolute range can sometimes not be possible due to unknowns.
Using (F:F) for instance can be a useful alternative as it looks at the
entire column, not just part of it.
However, is it possible to specify a start cell to search down from?
i.e.(F$10:F) This q is for both formulae and vb.
Thanks,
--
Traa Dy Liooar

Jock

unknown column length
 

The below will refer the cells F10:F65536
=OFFSET(F1,9,0,65536-9,1)

'to sum that range
=SUM(OFFSET(F1,9,0,65536-9,1))

'VBA to refer the range.
Range("F10:F" & Rows.Count)

Msgbox Range("F10:F" & Rows.Count).Address


If this post helps click Yes
---------------
Jacob Skaria


"Jock" wrote:

Stating an absolute range can sometimes not be possible due to unknowns.
Using (F:F) for instance can be a useful alternative as it looks at the
entire column, not just part of it.
However, is it possible to specify a start cell to search down from?
i.e.(F$10:F) This q is for both formulae and vb.
Thanks,
--
Traa Dy Liooar

Jock