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