select non contiguos columns by variable name

I have integer variables that define several columns of interest in a
worksheet. How do I select multiple non contiguous columns?

Example

parts = 3	' column 3 has parts info
dates = 6	' column 6 has date info
cost = 10	' column 10 has cost info

columns(parts, dates, cost).Select   this statement does not work

TIA

 
John Keith
kd0gd@juno.com
0
John
1/23/2010 10:32:37 PM
excel.programming 6508 articles. 2 followers. Follow

7 Replies
1241 Views

Similar Articles

[PageSpeed] 17

Hi,

Try this :

Range("parts, dates, cost").Select




"John Keith" <kd0gd@juno.com> a �crit dans le message de groupe de discussion : 
h0uml5d6lt7pclqj6ilh34lar227mqmvm4@4ax.com...
I have integer variables that define several columns of interest in a
worksheet. How do I select multiple non contiguous columns?

Example

parts = 3 ' column 3 has parts info
dates = 6 ' column 6 has date info
cost = 10 ' column 10 has cost info

columns(parts, dates, cost).Select   this statement does not work

TIA


John Keith
kd0gd@juno.com 

0
michdenis
1/23/2010 10:55:31 PM
With ActiveSheet
    Union(.Columns(parts), .Columns(dates), .Columns(cost)).Select
End With

But remember, it's very rare where you actually have to select a range to work
with it.

John Keith wrote:
> 
> I have integer variables that define several columns of interest in a
> worksheet. How do I select multiple non contiguous columns?
> 
> Example
> 
> parts = 3       ' column 3 has parts info
> dates = 6       ' column 6 has date info
> cost = 10       ' column 10 has cost info
> 
> columns(parts, dates, cost).Select   this statement does not work
> 
> TIA
> 
> 
> John Keith
> kd0gd@juno.com

-- 

Dave Peterson
0
Dave
1/24/2010 12:14:27 AM
On Sat, 23 Jan 2010 17:55:31 -0500, "michdenis"
<michdenis@hotmail.com> wrote:

>Try this :
>
>Range("parts, dates, cost").Select

I did try that and it did not work. But thank you for looking.


John Keith
kd0gd@juno.com
0
John
1/26/2010 12:16:04 AM
On Sat, 23 Jan 2010 18:14:27 -0600, Dave Peterson
<petersod@verizonXSPAM.net> wrote:

>With ActiveSheet
>    Union(.Columns(parts), .Columns(dates), .Columns(cost)).Select
>End With
>
>But remember, it's very rare where you actually have to select a range to work
>with it.

Dave,

Thank you that worked perfectly. And yes, I was able to merge the next
line that began Selection.

What are the pros/cons to using Select?


John Keith
kd0gd@juno.com
0
John
1/26/2010 12:18:34 AM
The cons are that you have to select something to work with it.  The code is
difficult to read/modify.  

The pros are that you can use what the macro recorder gave you.  But that
recorded macro is probably very messy to understand.

John Keith wrote:
> 
> On Sat, 23 Jan 2010 18:14:27 -0600, Dave Peterson
> <petersod@verizonXSPAM.net> wrote:
> 
> >With ActiveSheet
> >    Union(.Columns(parts), .Columns(dates), .Columns(cost)).Select
> >End With
> >
> >But remember, it's very rare where you actually have to select a range to work
> >with it.
> 
> Dave,
> 
> Thank you that worked perfectly. And yes, I was able to merge the next
> line that began Selection.
> 
> What are the pros/cons to using Select?
> 
> John Keith
> kd0gd@juno.com

-- 

Dave Peterson
0
Dave
1/26/2010 2:13:38 AM
| I did try that and it did not work. But thank you for looking.

Range("parts, dates, cost").Select

*** it works if "parts", "dates" and "cost" are names not variables
      representing a column number as it was the case in your question.
      I simply misinterpreted your request !




John Keith
kd0gd@juno.com 
0
michdenis
1/26/2010 4:12:54 AM
On Mon, 25 Jan 2010 23:12:54 -0500, "michdenis"
<michdenis@hotmail.com> wrote:

>
>| I did try that and it did not work. But thank you for looking.
>
>Range("parts, dates, cost").Select
>
>*** it works if "parts", "dates" and "cost" are names not variables
>      representing a column number as it was the case in your question.
>      I simply misinterpreted your request !

Ah, thank you. Someday I'm going to use range names and I'll add this
to my bag of tricks.


John Keith
kd0gd@juno.com
0
John
1/26/2010 4:43:58 AM
Reply:

Similar Artilces:

Column Headings #11
Can you add seperate column headings (A, B, C, ...) into one spreadsheet? I'm attempting to alter the column sizes half-way through the spreadsheet w/out affecting the upper column sizes... Coolumn width belongs to the entire column and cannot be altered in separate sections of that column. Gord Dibben Excel MVP On Tue, 8 Mar 2005 15:51:01 -0800, spencer4hire <spencer4hire@discussions.microsoft.com> wrote: >Can you add seperate column headings (A, B, C, ...) into one spreadsheet? >I'm attempting to alter the column sizes half-way through the spreadsheet >w/ou...

Text to columns
Once I use the Text to columns feature in Excel, it seems there is no way to turn it off. Anyone know if there is a way to reset this so that newly pasted text will not continue to get broken up (for example by the space delimiter) Presently the only way is to exit Excel and restart Excel - then pasted text all goes into one cell regardless of spaces. Hope I explained that well enough Al I may have been to hasty in making this assumption, it appears that the problem I described below is only happening on one workstation - this may indicate that the Excel Registry keys are in need of...

what is the function and name is of the symbol in each table cell.
Under Paragraph I clicked the Show/Hide Symbol icon so I can now see a symbol at the end of each text within a table cell. I wondered what that is so I tried to use Help to find out. I did find help that mapped a word (like paragraph) into a symbol. But I can't find anywhere where if I know the symbol it will tell me the meaning. Can you tell me how to find such info? Or maybe you can tell me what the function and name is of the symbol in each table cell. Thanks I'm sorry, I meant to sent this to the Word group. Of course, I wouldn't mind getting the info...

Column Reference to External Source As a Variable
Can anyone help me convert the column referenced in the formula below into a variable that the user can define? More specifically, I have several columns that I need to read from an external workbook (Short_Billy.xls). Each column to the right of column C represents an additional day out in a 14 day projection from today (whose data is held in column C). In cell I5 of my active workbook (Inventory.xls), I would like the user to be able to enter a value representing the number of days out they would like to see the projection for (0=today=Column C, 1=Tomorrow=Column D, etc.). In cell I6, I...

Is it possible to generate non-technical schema validation errors?
With the 1.0 Framework, I've worked out using the XmlValidatingReader. Since I'm using the validation errors as feedback to the end user, I'm hoping to get away from techy messages such as "The 'http://tempuri.org/XMLFile1.xsd:MaxDependents' element has an invalid value according to its data type. An error occurred at file:///c:/work/prodika/main/code/apps/schemavalidation/XMLFile1.xml(8, 25)." and go with a user friendly message of "Max Dependents must be between 0 and 10". I've scoured the newsgroups, MSDN and docs for creating custom valid...

How can I clear the last Data->Text to columns to formatting
I've noticed in Excel 2000 that if I paste text into various worksheets within a workbook each paste will assume the Text->Column formatting that I applied in the previous. How can I prevent it from happening ? Thanks Steve Just run another data|Text to columns against a dummy cell. Specify delimited, but remove all the check marks from all the possible delimiters. (alternatively, you can close excel and reopen it.) svaardt wrote: > > I've noticed in Excel 2000 that if I paste text into various worksheets > within a workbook each paste will assume the Text->Col...

Suggest Names While Completing .....
Is there anyway to remove a "suggested name" short of actually turning this feature off? Our CEO's PA has returned from leave but her home email address keeps popping up as an option. Ta Ian When Outlook suggests the name, highlight it with the arrow keys and then press the delete key. "Ian Kynaston" <IKynaston@nospam.propertyoz.com.au> wrote in message news:554f01c4743a$a34b6910$a601280a@phx.gbl... > Is there anyway to remove a "suggested name" short of > actually turning this feature off? > > Our CEO's PA has returned from leave b...

selecting a cell
I seem unable to select a single cell, or a single row--click on one in the normal manner, and the two below also highlight, then delete or whatever command is given. If I input a number/text, that just goes into the one cell. tapping F8 increases this to two wide and three high automatically selected. Also, very slow to do almost anything. Thanks Pat, Are you by any chance using Excel 2007? If so there is a known bug that causes multiple cell selection and I understand this has been reported to Microsoft. If you take the zoom level up and down this is reported to cl...

Excel ask duplicate NAMES when duplicate a worksheets
I have added a NAME called "Above" where point to the cell just above the current cell. The formula is "=INDIRECT("R[-1]C",)" In some workbook, when I duplicate a worksheets, this name will remain silent and work ok. But in some workbooks, when I first duplicate a worksheets, the same name ABOVE will be duplicate and a new local name (belongs to that new worksheet) will be created. If I further duplicate that new worksheets in to a new worksheets, the third worksheets will be warned that a dupicate NAME is existed and ask whether refer to another name or use a ne...

Excel 2003 Copy/Paste filtered column
I have a filtered column on my spreadsheet. I have copied the column, changed the figures and then tried to paste it back on to the filtered column. It is not copying over the original filtered column but rather over cells that have been filtered out. The worksheet/cells are not protected. What could the problem be? Kind Regards Heather That's the way pasting works. It'll hit the visible and hidden cells. Heather wrote: > > I have a filtered column on my spreadsheet. I have copied the column, > changed the figures and then tried to paste it back on to the filter...

Right click in Pivot Table or on Entire Column
I have added items to the right-click menu that popups up when you have a cell or cells selected. But when you are in a Pivot Table or have an entire column selected the right-click popup is different. Is there a way to add an item to the right-click popup menu when you are in a Pivot Table or have an entire column selected? Thank you for your help. Steven Never mind. This one was right in the help section. I should have looked first. Thank you, Steven "Steven" wrote: > I have added items to the right-click menu that popups up when you have a ...

how to automatically suppress space before after column break?
Having Spacing Before and After on some of the styles, I seem to be unable to have the space before at the beginning of a column automatically dismissed when applying a column break. I have tried a couple of options under compatibility, but to no avail. This in on Word 2003. The No HTML function + No Space Before after column break do not solve the problem. Can you help please? Tools | Options | Compatibility: Suppress Space Before after a hard page or column break. If this isn't working, then check to make sure you don't have an empty paragraph before the first text pa...

matching columns of numbers
In EXCEL 2000 for Windows, I have two columns of numbers. Column A has 500 numbers, Column B has 1000 numbers. I need to know which cells in Column A have a match in Column B, and if so, what is the Cell (or row number) in B that matches to that particular cell in A. How can I do this? Thank you for your help. ** Posted via: http://www.ozgrid.com Excel Templates, Training, Add-ins & Business Software Galore! Free Excel Forum http://www.ozgrid.com/forum *** Hi try the following: - insert a new column between A and B (so make B the new C column) enter the following in B1 =IF(ISNA(MATCH...

exchange 5.5 will not keep new server name.
We have exchange 5.5 in enviroment where windows server 2003 is setup along with active directory and active direcitory intergrated zone which is replicated to other servers. Problem: I enter the Exchange Server Name followed by the mailbox name then I select the button [CHECK NAME] In the exchange server name field after entering the new exchange server name the old exchange server name is displayed instead. This only happens in office 2003 professional and not in office 2000 professional "old exchange server name" How did you go about changing the name of the server...

Can I show server name instead of drive letter?
When using the =Cell("filename") function, I would like to show the actual server instead of a drive letter because the letter is dependent on where the server is mapped and not all users have the same mapping. Is this possible? Try using: \\ServerName\drive\... where ServerName is the name of the server and drive is the appropriate drive, and then find the file on the drive from that point. "bfant" wrote: > When using the =Cell("filename") function, I would like to show the actual > server instead of a drive letter because the letter is dependent on...

Count the text in a column
I would like to count the text in a column then for it to add a figure in another cell if it meets the text criteria Thanks! Do you mean count the characters? If so =SUM(NOT(ISNUMBER(A1:A20))*LEN(A1:A20)) as an array formula (committed with Ctrl-Shift-Enter) -- HTH RP (remove nothere from the email address if mailing direct) "Peter Curtis" <PeterCurtis@discussions.microsoft.com> wrote in message news:94C093C4-38DC-4989-846A-9352F3298B7C@microsoft.com... > I would like to count the text in a column then for it to add a figure in > another cell if it meets the t...

Selecting cell value for a sum, based on a condition
Trying to come up with a formula or method that will enable me to sum values based on a condition. For example, I have three columns which contain a condition and two amounts. If the condition is of the 'each' variety, one value will be used in the sum. If the condition is of the "square foot" variety, another value will be used. Here is a small diagram that may help visualize this: A B C D 1 Measure Unit Cost S.F. Cost Summed Total 2 Each 3.00 .30 3 S.F....

forms and column lengths
Is there a way to have excel do an auto "carriage return" to the next row when you have reached the specified maximum number of characters in the row above?? Hi there's n o bulit-in feature for this -- Regards Frank Kabel Frankfurt, Germany "Blair" <Blair@discussions.microsoft.com> schrieb im Newsbeitrag news:C1D2CAAD-C4E9-492A-ADF4-CBDB659514A3@microsoft.com... > Is there a way to have excel do an auto "carriage return" to the next row > when you have reached the specified maximum number of characters in the > row > above?? ...

Highlight color for selected items
How can I find out what color is used for highlighting selected items (for example in Windows Explorer)on a users computer? Normally it is a blue color, but a user can change that. "Urban Olars" <anonymous@discussions.microsoft.com> wrote in message news:071c01c3c941$3db2c890$a401280a@phx.gbl... > How can I find out what color is used for highlighting > selected items (for example in Windows Explorer)on a users > computer? > Normally it is a blue color, but a user can change that. See if it's GetSysColor/COLOR_HIGHLIGHT. -- Jeff Partch [VC++ MVP] Take a ...

Routing Restrictions button unavailable (gray) no matter what options selected in IMS-->Routing tab
I am running Exchange 5.5 SP4. In Connections-->IMS-->Routing Tab, my "Routing Restrictions" button is unavailable (greyed-out) no matter what options I select. Any suggestions? Thank you. Try installing Exch Admin prog on other machine, apply SP4 and see if you can change it from there. Post back with details. ryanadmin wrote: > I am running Exchange 5.5 SP4. In Connections-->IMS-->Routing Tab, my > "Routing Restrictions" button is unavailable (greyed-out) no matter > what options I select. Any suggestions? Thank you. ...

Selecting a column with an integer
Sub ColumnSelection() ' Selecting a column with an integer ' Please show me how to eliminate the use of Cells(1, 1) Dim r As Integer Dim c As String Dim numericcolumn As Integer Dim alphabetcolumn As String numericcolumn = 4 ' in practice 4 is the resultant of an equation alphabetcolumn = "=CHAR(" & numericcolumn + 64 & ")" Cells(1, 1) = alphabetcolumn ' I like to eliminate the use of Cells(1, 1) c = Cells(1, 1).Value ' I like to eliminate the use of Cells(1, 1) Cell...

how to convert a CString variable to unsigned short array one?
Hi, In my unicode VC6 app, how to convert a CString variable to unsigned short array? ....... CString strRst; unsigned short strOleChars[100]; strOleChars = strRst; //??? Thank you. Is ConvertStringToBSTR() what you are looking for? AliR. "David" <David_Wang_Xian@hotmail.com> wrote in message news:e6gbA3avFHA.720@TK2MSFTNGP15.phx.gbl... > Hi, > In my unicode VC6 app, how to convert a CString variable to unsigned short > array? > ...... > CString strRst; > unsigned short strOleChars[100]; > strOleChars = strRst; //??? > > Thank you. > > ...

BP Req Mgmt Lookup should show additional columns
When doing a lookup I should be able to configure the columns that I would like to see visible on the lookup. For example, when looking up an item only item number and item description are visible fields. I would like to configure the lookup to show additional fields, like the vendor name. ...

Prevent copy and paste in one column
I am having trouble trying to prevent copying and pasting in one specific column. The code refers to the specific range, but yet it prevents copying and pasting on the whole worksheet. Option Explicit Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Not Intersect(Target, Columns("H:H")) Is Nothing Then Application.CellDragAndDrop = False Application.CutCopyMode = False Else Application.CellDragAndDrop = True End If End Sub Works for me in Excel 2003 Gord Dibben MS Excel MVP On Fri, 30 Apr 2010 11:42:01...

Non AD emails going to 1 user
I have an Exchange 2003 server running on SBS 2003 the issues is one user is getting all the emails sent to him that look like they are coming from his domain. For example his email is user@mydomain.com but in his inbox he is getting XYZ@mydomain.com but XYZ is not in the AD or has a email box set up on this server. Why is getting this non AD email and how can I stop it. Thanks in advance Are you sure it's not a SPAM where the spammer may have simply put in 123abc@mydomain.com and BCC it to all possible conceivable names @mydomain.com?? R Green "LaOVis" <LaOVis@discuss...