• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 199
  • Last Modified:

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

0
ronanm1
Asked:
ronanm1
  • 5
  • 3
1 Solution
 
[ fanpages ]IT Services ConsultantCommented:
Sorry... which line is in bold?
0
 
ronanm1Author Commented:
Range(Cells(X, 1), Cells(4, y)).Select
0
 
[ fanpages ]IT Services ConsultantCommented:
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.
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
[ fanpages ]IT Services ConsultantCommented:
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.
0
 
[ fanpages ]IT Services ConsultantCommented:
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]

?
0
 
ronanm1Author Commented:
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
0
 
[ fanpages ]IT Services ConsultantCommented:
Thanks for your clarification.

What range(s) do you need, then?

Top 3 from column A & column B (assuming headings on row 1):
Range("A2:B4").Select

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

BFN,

fp.
0
 
ronanm1Author Commented:
Simply beautiful! Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now