Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Finding a string of unknown length in a string of unknown length, Help! | Excel Discussion (Misc queries) | |||
Unknown column length | Excel Programming | |||
SMALL() on unknown length of list | Excel Worksheet Functions | |||
How do I sum a column of unknown length using a macro | Excel Programming | |||
Sum unknown length column data in VBA | Excel Programming |