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
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 ]
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
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
Like this thread? Share it with others