Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 3 of 3

Thread: Dynamic arrays

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Location
    England, UK.
    Posts
    526
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  2. #2
    MrExcel MVP
    Colo's Avatar
    Join Date
    Mar 2002
    Location
    Kobe, Japan
    Posts
    1,456
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    2 Thread(s)

    Default

    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

  3. #3
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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


Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •