Link to home
Start Free TrialLog in
Avatar of ronanm1
ronanm1

asked on

Dynamic Ranges in Excel

Hi,

I have the following code:

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



    Range("A2").Select
    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, _
        DataOption1:=xlSortNormal
    Range(Cells(X, 1), Cells(4, y)).Select
      Selection.Copy
    Cells(d, b).Select
    ActiveSheet.Paste
   
         y = y + 1
    d = d + 5
      Next

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

Avatar of [ fanpages ]
[ fanpages ]

Sorry... which line is in bold?
Avatar of ronanm1

ASKER

Range(Cells(X, 1), Cells(4, y)).Select
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

BFN,

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

BFN,

fp.
I'm confused with all the variables.

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

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

?
Avatar of ronanm1

ASKER

Hi,

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
ASKER CERTIFIED SOLUTION
Avatar of [ fanpages ]
[ fanpages ]

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of ronanm1

ASKER

Simply beautiful! Thanks