Dynamic arrays

[RET79]

My macro used this before to be a dynamic array


Range([B1], [B1].End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select

Now, I have 3 columns now like this col(A:C)

CellRef Row Col
B2 2 2
B4 4 2

But now, instead of these being numbers, they are forumlas. For instance, the forumlas in column B are of the form:

=IF(A2="","",CELL("col",INDIRECT(A2)))

in B2 and similarly

=IF(A6="","",CELL("row",INDIRECT(A6)))

in C2.

Now, the problem is that the dynamic range now INCLUDES the cells which contain formulas, which is very annoying. Is there a way of setting the dynamic array so that it only picks up the cells in the column which has a value?

Thanks.

[ This Message was edited by: RET79 on 2002-03-26 19:22 ]

[Colo]

Hi. I'm not sure but please try this.

Sub test()
Dim rngConstants As Range
Set rngConstants = Columns(2).Resize(, 2).SpecialCells(xlCellTypeConstants)
rngConstants.Select
End Sub

[Dave Hawley]

Hi RET79


Why can't you use the method I showed in your original post on this? It will allow the users to simply enter the text, say "b20" in then cell.

You may also be interested in Dynamic ranges:
http://www.ozgrid.com/Excel/DynamicRanges.htm