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)).Sel ect
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..
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)).
Range(Selection, Selection.End(xlDown)).Sel
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..
Sorry... which line is in bold?
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.
Option Explicit
Public Sub Select_Two_Columns()
Dim intColumn As Integer
For intColumn = 2 To 256
Range(Columns(1).Address & "," & Columns(intColumn).Address
' 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.
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]
?
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]
?
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Simply beautiful! Thanks