exclude columns in macro

I have a simple macro below. I would like to exclude 
certain Columns (c). So instead of reading c=5 to 16, it 
will be 5 to 8, 10-13....... 

p = "C:\Documents and Settings\"
    f = "Stuff.xls"
    s = "Sheet1"
    Application.ScreenUpdating = False
    For r = 12 To 72
       For c = 5 To 16
                a = Cells(r, c).Address
            Cells(r, c) = GetValue(p, f, s, a)
        Next c
    Next r
    Range("D:F,I:K,N:O").Select
    Selection.NumberFormat = "#,##0_);(#,##0)"

Also, is there a way for me to identify these columns 
later on when I am formatting them? So, instead of 
D:F,I:K,N:O, i'll have 5-6, 8-10 and so on.

Thanks
0
anonymous (74718)
2/24/2004 4:30:41 PM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
489 Views

Similar Articles

[PageSpeed] 22

I might be inclined to attack this way:

Sub Example()
    Dim Cell As Range
    Dim Rg As Range
    Set Rg = Range("D12:F72,I12:K72,N12:O72")
    For Each Cell In Rg
        Cell.Value = GetValue(p, f, s, Cell.Address)
    Next
    Rg.NumberFormat = "#,##0_);(#,##0)"
    ''or
    'Rg.EntireColumn.NumberFormat = "#,##0_);(#,##0)"
End Sub


-- 
Jim Rech
Excel MVP


0
jrrech (1933)
2/24/2004 6:02:14 PM
Reply:

Similar Artilces:

Replace a certain Character in a column
To make a long story short, I have a lot of serial numbers (for example SWGMM036478, and I need to remove all of the "S" character automatically. Is there anyway that I can do this easily in Excel. have over 600 serial numbers and they all have the letter "S" at th beginning and they are not supposed to be there. Any tips? Thanks -- Message posted from http://www.ExcelForum.com Select the cells that contain the serial numbers. Choose Data>Text to Columns Select Fixed Width, click Next In the Data Preview window, click after the S, to add a break line. Click N...

align 3 concatenated strings for column appearnace
I combined 3 cells to make a drop down list. However I am not pleased with the final appearance as the texts scrawls over the page. I'd like to align the text strings so that its appearance is more appealing while still using the drop down lists. Thanks for your help. "Dano" wrote > I combined 3 cells to make a drop down list. > However I am not pleased with > the final appearance as the texts scrawls over the page. > I'd like to align the text strings > so that its appearance is more appealing > while still using the drop down lists. Not sure.. we c...

Breaking up a column
Is there a simple to way to extract field data to insert in their own field? I have a list with the City, State and Zip in one column as follows: Erving, MA 01344 Is there a way to extract the City an put it into a seperate column? So I can end up with the City State and zip in their own columns. Thanks Try Data -- Text to Columns (Delimited, Delimiters = Comma *and* Space, Finish). You'll need to make sure you have a couple of blank columns to the right of your list. HTH, Andy Nevermind I figured it out. I placed the column in Word and used find and replace to insert ",&q...

Datedif exclude weekends
Hello newsgroup gurus and users. Your kind assistance please. This question has probably been asked a millions times but I cant find it. Does any one have a solution to this. I want the date difference between two dates but exclude weekends. Many thanks Paul Paul, The NETWORKDAYS function will return the number of days, excluding weekend and optionally holidays, between two dates. See help for NETWORKDAYS for more information. This function is part of the Analysis Tool Pack add-in so you must have that add-in loaded; otherwise, you'll get a #NAME error. -- Cordially, Chip Pe...

gci and -include and -exclude
I am trying get a limited set of files from a directory tree. The following is what i want but it doesn't work. I get everything. get-childitem -LiteralPath \rm5_0_code_h\source -include *.frm,*.ctl,*.bas,*.cls,*.cs,*.cpp,*.hpp,*.sql,*.java -Recurse I have even tried to exclude what i don't want but it doesn't seem to matter. get-childitem -LiteralPath $path -include *.frm,*.ctl,*.bas,*.cls,*.cs,*.cpp,*.hpp,*.sql,*.java -recurse -exclude *alter*.sql,*.dll,*.pdb,*.ncb,*.tlb,*.scc,*.bmp,*.ico i still get them all. what am i doing wrong? dan you have to a...

Multiple Columns
How can I get Excel to convert one long column of data into multiple columns for printing. so as not to wast paper ( Without cutting and pasting ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com See http://www.mvps.org/dmcritchie/excel/snakecol.htm -- Regards, Peo Sjoblom "seendcleeve" <seendcleeve.x59yy@excelforum-nospam.com> wrote in message news:seendcleeve.x59yy@excelforum-nospam.com... > > How can I get Excel to convert one long column of d...

How do I markup a column of numbers without adding a column?
I am trying to markup a column of numbers but do not wish to add a column (eg. Column A * .05 for Column B). Is this possible? You mean you want to add 5% to a range of cells? If so, Enter 1.05 in an empty cell. Copy that cell. Highlight the range you want to change Right-click, choose Paste Special...>Multiply>OK Delete the 1.05 if desired. Regards, Fred "Afreshb" <Afreshb@discussions.microsoft.com> wrote in message news:6E855D4B-0772-4AE0-BA3A-B6356D68A123@microsoft.com... >I am trying to markup a column of numbers but do not wish to add a column ...

Exclude a cell
Excel 2002 Any one know how I can sum all the cell values in a column except the value of the last cell to be entered which could be say half-way down the column? Is there a function to exclude a particular cell from "sum" ? I know a lot of ppl are far more advanced than I am but we all start somewhere lol Thanks for your help Dale To exclude cell A16: =SUM(A:A)-A16 Not very elegant, but effcient! Cheers, -- AP "DF2507" <nospamthx@blueyonder.co.uk> a �crit dans le message de news: xB6sg.51172$181.12831@fe3.news.blueyonder.co.uk... > Excel 2002 > &...

Combinations from values in a column
Hi there, I'm trying to figure out how to generate all combinations from a list of N values in a column, let's say column A. In particular, I want to have all combinations of 2 values, 3 values, 4, ... up to 7 values. To give an example: Let's assume I have a list of only 3 values (1,2,3) for which I want to have all combinations of two values. In this case, the result would be 1,2; 1,3; and 2;3. The ordering of the values does not matter, i.e. duplicates should be eliminated. I hope I could explain it sufficiently. I would be grateful for any help. Thanks, Andreas Use the Co...

Excluding zeros
I have stock data in excel which I'd like to chart using the high low close option. Problem is that the data contains zeros on some days. Can I get excel to ignore the zeros instead of plotting them. If not what's the best way of converting these zeros to blank cells so that Excel does not chart them. Thank you Hi Ben, Two ways: 1) replace zeros (or missing data) by =NA() 2) Tools/Options/Chart and specify what is to be done with missing values. Bernard "Ben" <anonymous@discussions.microsoft.com> wrote in message news:075901c3bff5$94a57530$a001280a@phx.gbl... &...

enter data in cell then run macro automatically
Hi, Is it possible that I can enter data into a cell and then the pressing of the enter key will cause a macro to run? Rick Yes. That type of macro is run from Event code. For more on events see Chip Pearson's site at http://www.cpearson.com/excel/events.htm Or David McRitchie's site at http://www.mvps.org/dmcritchie/excel/event.htm Gord Dibben Excel MVP On Tue, 25 Jan 2005 21:04:42 -0000, "R D S" <RDS@members.v21.co.uk> wrote: >Hi, > >Is it possible that I can enter data into a cell and then the pressing of >the enter key will cause a macro to...

Newspaper Columns
I have a long list of data consisting of three narrow columns. When I print the sheet, I'd like to repeat the set of three columns a few times per page (exactly like a telephone book) to prevent wasting paper. Can't find how to do this in the help index. Any suggestions?? thanks. Dave Dave One VBA routine...... Will overwrite data on the sheet. Make a copy of your original sheet if you want to preserve it. Public Sub Snake3to9() Dim myRange As Range Dim colsize As Long Dim maxrow As Long Const numgroup As Integer = 3 Const NUMCOLS As Integer = 9 O...

"Today" column not updating
A few times in the past week this has happened: I connect and update my money info online. Money shows me the price change for all of my funds but - for SOME funds only - does NOT update the "Today" column. I was surprised to see that - I thought that the math was done at MY pc - not online. So if I owned 100 shares of XYZ and YESTERDAY it was $20 per share (Money knows that - I update everyday) and today it is $21/share on my screen I see 100 shares $21 per share - it DOES now the new price, but in the TODAY column it does not show $100 as expected - it's blank. So I selecte...

formula to compare columns?
Hello Does anyone know how to write a formula that compares two adjacent columns of names and lists those names that aren't exact matches in a blank column. The columns of names are in ascending order and the quantity of names in each column may be different Thanks btk Hi enter the following in C1 =IF(COUNTIF($A$1:$A$1000,B1)>0,B1,"") and copy down -- Regards Frank Kabel Frankfurt, Germany btk wrote: > Hello, > > Does anyone know how to write a formula that compares two adjacent > columns of names and lists those names that aren't exact matches in a >...

Row and Column Viewing #2
Gord, Can the same code be used for singlesheets vs the workbook -- RudeRa ----------------------------------------------------------------------- RudeRam's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=881 View this thread: http://www.excelforum.com/showthread.php?threadid=26574 Rude The code I gave works only on one sheet......Sheet1....and does not affect any other sheet in the workbook. Code is placed in the ThisWorkbook module and runs when workbook is opened. Copy the code then right-click on the Excel Icon just left of "File" on main Men...

IVR10015.DAYTOEXP column not found
I am doing an upgrade of GP from version 7.5 to 8.0 (eventually going to 9.0) on a test server. I am running into an issue where when I try to run the GP Utilities to upgrade the companies after installing GP8.0 Service Pack 4A, I get a message saying that the DAYTOEXP column cannot be found. I looked in the mfg_update.cmp SQL script and I can see that the script is trying to add the INCL_EXP_TO_ENG column to IVR10015, but it appears to be expecting the DAYTOEXP column to already be in this table, but it is not. When is the DAYTOEXP column added to IVR10015? I checked our GP7.5 environ...

Total budget column filled with Zeros 2002
Annual budget report shows monthly budget amounts and actuals but onl;y the actuals appear in the total column. How to fix please. -- Thanks Chris ...

column widths #2
Hello everyone, this is my first time posting and i'm really learning a lot in thi forum. I have a question and i hope someone could please help me. I'm currently entering information on a excel spreadsheet that created that is 7 pages long. i've been told recently that th spreadsheet needs to be reduced to 3 pages. i've reduced the font, bu my problem is i'm not sure how to make excel change each page t reflect different column widths for each page. i've merged variou cells, and that has helped a little, but for the most part, each colum seems to still take on the ...

Printing the last column on every page printed.
I want to be able to print the last column of my spreadsheet (Total column) on every page that is printed from this workbook. How can I do this? I'm stumped... Thanks for your help! Are all the last columns in the same location (column X for example)? If yes, you could group all the sheets (click on the first, ctrl-click on subsequent). then select column X File|print|Select (bottom left corner) Do a preview first. But this prints each worksheet (column) on a different sheet. ====== You could have a macro that built a new worksheet and just copy|pastes values| that last column ...

excluding repeating values
I have a column wuth date and some entries are repeating. I want to copy that data to another column, but do not want to copy duplicate values ( I want to exclude repeating values when copying and pasting). Is there an option to do that? Thanks. Neda Take a look at http://contextures.com/xladvfilter01.html#FilterUR In article <CE251167-21F0-470D-9641-508FD96D54FD@microsoft.com>, "neda5" <neda5@discussions.microsoft.com> wrote: > I have a column wuth date and some entries are repeating. I want to copy that > data to another column, but do not want to c...

create chart from series grouped by value in another column
How to create a chart from series grouped by a value from another column? Example The sheet has these rows: A, DATE, 1 A, DATE, 6 A, DATE, 7 B, DATE, 3 B, DATE, 15 B, DATE, 6 B, DATE, 6 A{1,6,7} is a series and B{3,15,6,6} is a different series. I have around 2000 different series like that in my excel. How can I auto-create charts for each series (i.e. not selecting each series one by one)? Creat another Sheet with the data series in the order you wish to graph them. "HarCo" wrote: > How to create a chart from series grouped by a value from another column? > Example > ...

adding ever other column
how do i add every other column without doing the forumla as A1+C1+E1 ect ect? I want to be able to total multiple columns. Thanks Look in the help index for MOD. -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com "gma" <gma@mircosoft.com> wrote in message news:93F9A334-11BD-4E64-B752-358E81A6C690@microsoft.com... > how do i add every other column without doing the forumla as A1+C1+E1 ect > ect? I want to be able to total multiple columns. Thanks hi, Change the range to suit =SUMPRODUCT(MOD(COLUMN(A1:M1),2)*A1:M1) Mi...

how to specify column width in column graph?
I'm creating a column graph which columns I need to represent a certain value. In other words, the column should be representative of its inputs, both in terms of width and height. For example, one column's width should be representative of 150,000 and its height should be representative of 6.42. If anyone has done this, please advise. Thanks. You could look at: http://peltiertech.com/Excel/Charts/Brickyard.html http://andypope.info/charts/colwidth2.htm Hope this helps. Regards, Leni -- LeninVM ----------------------------------------------------------------------- LeninVMS&#...

sorting columns with formulas
How do I sort columns, which have data that is being used in formula in a different column, without any of the values changing afte sorting -- still learnin ----------------------------------------------------------------------- still learning's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2390 View this thread: http://www.excelforum.com/showthread.php?threadid=37576 Only way I know of would be to turn the formulas into values by copy and paste special as values Regards, Peo Sjoblom "still learning" wrote: > > How do I sort columns, w...

How do I make a macro prompt for user input
I want my macro to prompt the user to input data. Hi look at inputbox in the VBA help -- Regards Frank Kabel Frankfurt, Germany "Chad" <Chad@discussions.microsoft.com> schrieb im Newsbeitrag news:5EF0B644-C887-49AD-8B46-FE49C2FBEC2C@microsoft.com... > I want my macro to prompt the user to input data. ...