[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Dynamic Ranges in Excel

Posted on 2005-04-13
8
Medium Priority
?
197 Views
Last Modified: 2010-05-02
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
Comment
Question by:ronanm1
  • 5
  • 3
8 Comments
 
LVL 35

Expert Comment

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

Author Comment

by:ronanm1
ID: 13770859
Range(Cells(X, 1), Cells(4, y)).Select
0
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 13770866
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 13770879
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
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 13770894
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
 

Author Comment

by:ronanm1
ID: 13770939
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
 
LVL 35

Accepted Solution

by:
[ fanpages ] earned 600 total points
ID: 13771083
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
 

Author Comment

by:ronanm1
ID: 13771283
Simply beautiful! Thanks
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Suggested Courses

834 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