Dynamic Ranges in Excel

Posted on 2005-04-13
Last Modified: 2010-05-02

I have the following code:

Dim zz As Range
Dim X, y, z, w, a As Long

    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Name = "zz"
    y = 2
    X = 2
    z = 4
       a = 8
    b = 1
    c = 3
    d = 11
    For Each Column In Selection
    Selection.Sort Key1:=Cells(X, y), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
    Range(Cells(X, 1), Cells(4, y)).Select
    Cells(d, b).Select
         y = y + 1
    d = d + 5

I only want to select 2 columns each time, column A and another column. In the above, in the first run, it will select cols A and B. The second run/loop selects A and B and C. I only want to select A nd C, or 2 column combinations at a time i.e. A+B,A+C,A+D,A+E…

How can I avoid the cycle selecting more than 2 columns each time. Ideally, I would like to only edit the line in bold..

Question by:ronanm1
    LVL 35

    Expert Comment

    by:[ fanpages ]
    Sorry... which line is in bold?

    Author Comment

    Range(Cells(X, 1), Cells(4, y)).Select
    LVL 35

    Expert Comment

    by:[ fanpages ]
    You can select two columns (A+B, A+C, A+D, A+E, etc) like this:

    Option Explicit
    Public Sub Select_Two_Columns()

      Dim intColumn                                         As Integer
      For intColumn = 2 To 256
          Range(Columns(1).Address & "," & Columns(intColumn).Address).Select
          ' Perform code on Selection here
      Next intColumn
    End Sub


    LVL 35

    Expert Comment

    by:[ fanpages ]
    Replace your highlighted line [Range(Cells(X, 1), Cells(4, y)).Select] with:

    Union(Cells(X,1), Cells(4,y)).Select

    If you just want to select two individual cells.


    LVL 35

    Expert Comment

    by:[ fanpages ]
    I'm confused with all the variables.

    Are you trying to select whole columns, or individual cells within separate columns?

    Columns 1 & 3 (i.e. A & C)
    Cells(4,1) & Cells(7, 3) [A4 & C7]


    Author Comment


    I am looking at data like this:

    Manager      QuarterlyExcess      1 Year Excess
    A      -2.04                           -2.36
    B      -2.04                           11.55
    C      -1.61                            1.55
    D      -1.37                            0.77

    So the first cycle sorts the managers by Quarter Excess, then pulls the top 3 managers and their respective quarter excess values
    Then it sorts by 1 Year Excess, pulls the top 3 managers and their respective 1 year excess etc.

    The Union command only pulls individula cells. I need the range
    LVL 35

    Accepted Solution

    Thanks for your clarification.

    What range(s) do you need, then?

    Top 3 from column A & column B (assuming headings on row 1):

    Then Top 3 from column A & column C:
    Union(Range("A2:A4"), Range("C2:C4")).Select



    Author Comment

    Simply beautiful! Thanks

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    Introduction While answering a recent question ( in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
    Article by: Martin
    Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
    Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
    This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now