unknown column length

J

Jock

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

joel

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

muddan madhu

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

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

Jacob Skaria

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
 
Top