Select Range

Having trouble selecting several non-contiguous ranges.  The
spreadsheet I am working with is apprx. 300 rows by 110 columns.  The
sheet is subtotaled.  I need to copy data and move to a summary
worksheet.

There is a subtotaled section entitled 'Risk'.  Within this section,
the only data required is Country, Customer, 3Q (orders, revenue,
gross margin), and 4Q (orders, revenue, gross margin); columns B, E,
AQ, AR, AS, BK, BL, and BM - respectively.  The section title is in
cell A162 and the data is in A163:CY200.

I would prefer to select all of the data from the section at once, but
help selecting them separately will be greatly appreciated.

Areas of concern:
1.) Section is currently 39 rows, but will change over time.
2.) I do not maintain the spreadsheet.  At the end of the month, it is
placed in shared mode.  3Q and 4Q columns could change, i.e., I do not
expect them to remain in AQ, AR, AS, etc.
3.) Must capture all rows of data in 3Q and 4Q groupings for all rows
in Country and Customer.  In detail for this month (36 rows), there
are 36 entries for Country and Customer (both are text), but numerical
data in 3Q and 4Q can be null, black, 0, positive, or negative.  Using
End(x1Down) only yeilds 2 rows of numerical data for 3Q; 1st row is
blank, 2nd row is negative, and 3rd row is blank.  But there is data
in 12 additional rows.

If someone can please help.
BTW - I have not written the Copy/Paste portion because I have not
gotten the selection portion to work.

Sub SelectData()
'
' SelectData Macro
' Macro Selects Data from Detail Tab and Copies to Risks and
Opportunities Tab
' Data Copied Includes Country, Customer, and Quarterly Orders,
Revenue, and Gross Margin
' Macro Written 08/04/2004 by GibbsKJ
'

'
    Dim Country As Range
    Dim Customer As Range
    Dim Quarter3 As Range
    Dim Quarter4 As Range
    Dim Data As Range
    Sheets("2004 Detail").Select
    Cells.Find(What:="risk", After:=ActiveCell, LookIn:=xlFormulas,
LookAt _
        :=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:= _
        False, SearchFormat:=False).Activate
    Range(ActiveCell.Offset(1, 1), ActiveCell.End(xlDown)).Select
    Set Country = Range(ActiveCell.Offset(1, 1),
ActiveCell.End(xlDown)).Select
    Range(ActiveCell.Offset(1, 4), ActiveCell.End(xlDown)).Select
    Set Customer = Range(ActiveCell.Offset(1, 4),
ActiveCell.End(xlDown)).Select
    Range(ActiveCell.Offset(1, 42), ActiveCell.Offset(0,
2).End(xlDown)).Select
    Set Quarter3 = Range(ActiveCell.Offset(1, 42),
ActiveCell.Offset(0, 2).End(xlDown)).Select
    Range(ActiveCell.Offset(1, 62), ActiveCell.Offset(0,
2).End(xlDown)).Select
    Set Quarter4 = Range(ActiveCell.Offset(1, 62),
ActiveCell.Offset(0, 2).End(xlDown)).Select
    Set Data = Union(Country, Customer, Quarter3, Quarter4)
End Sub

With the following I know there should be 36 rows, so I need a range
that starts at the offset from the reference point and ends at the
Offset(Last RowIndex of rngTest, # of Colums specified).  Is this even
possible?  Much thanks and gratitude in advance.

Sub RangeTest()
    Dim rngTest As Range
    Sheets("2004 Detail").Select
    Cells.Find(What:="risk", After:=ActiveCell, LookIn:=xlFormulas,
LookAt _
        :=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:= _
        False, SearchFormat:=False).Activate
    ActiveCell.Offset(1, 1).Select
    Range(ActiveCell, ActiveCell.End(xlDown)).Select
    Set rngTest = Selection
    MsgBox rngTest.Rows.Count 'number of rows
End Sub
0
8/5/2004 11:35:46 PM
excel 39879 articles. 2 followers. Follow

0 Replies
402 Views

Similar Articles

[PageSpeed] 5

Reply:

Similar Artilces:

Selecting X cells based on answer to formula
Hi, I am trying to put together a spreadsheet that performs a couple of tasks. The first is basic resourcing which i am ok with, sort of how many heads and how many hours available means X cases will be done. The next thing I need to do is use the X cases result to identify from a list the oldest date and work through sequentially X dates in that column, which will not be consecutive, and tell me what the date of that case is. So if there were 10 cases done I would like one cell saying the oldest case worked on was - first date in column. And following doing the 10 cases I'd lik...

CFileDialog selecting directorys only
I Have a CFileDialog and I just want to be able to select directorys no files. Can this be done? use SHBrowseForFolder http://msdn.microsoft.com/library/default.asp?url=/library/en-us/shellcc/platform/shell/reference/functions/shbrowseforfolder.asp HTH, "Chris Baker" <ChrisBaker@discussions.microsoft.com> wrote in message news:5185346E-6097-4B9C-89B1-D6588C87249A@microsoft.com... >I Have a CFileDialog and I just want to be able to select directorys no >files. > > Can this be done? > You also might take a look at www.codeproject.com/dialog/cfolderdialo...

Averaging Selected Records
Hi, I have a continuous form with lots of data on it. In the form footer I would like to average certain fields by checking a checkbox next to the fields (in the detail section) I would like included in the average. I am having a brain fart on this and just not getting it to average the selected records. Can anyone point me in the right direction? Thanks "Lythandra" <Lythandra@discussions.microsoft.com> wrote in message news:262B5AEB-2692-4561-824C-D6FC7465FC1D@microsoft.com... > Hi, > > I have a continuous form with lots of data on it. > > In the form ...

How can I print only select sheets in my workbook?
My workbook has 12 sheets, but I want to print the second through ninth sheet exclusively, each time I hit print. (I do this once a week.) How do I do it? Excel 2007 Select sheet2 then hold SHIFT key and click on sheet9 File>Print>Active Sheet(s) Gord Dibben MS Excel MVP On Thu, 27 Mar 2008 19:14:00 -0700, MVictoreen <MVictoreen@discussions.microsoft.com> wrote: >My workbook has 12 sheets, but I want to print the second through ninth sheet >exclusively, each time I hit print. (I do this once a week.) How do I do it? >Excel 2007 Hi MVictoreen! As an optional m...

How do I pivot a range of cells, i.e. from column to row?
How do I pivot/transpose the data from a range of cells from, say, a column to a row? I imagine it should be very simple, but I haven't found out how yet. For instance: row# 1 data 1 2 data 2 3 data 3 4 data 4 to column# 1 2 3 4 data1 data2 data3 data4 ...

New contacts not showing when selecting the "To" button on a new e
Hello, If I add a new contact in Outlook 2003. Then I go to compose a new email. When I select the "To" buttom and either search or look for the new contact I created. It doesn't not show up. Any help would be appreciated. Thanks, Tony Two possibilities: 1. Your Contact doesn't have a resolved, valid electronic address 2. You did not add the Contact to the same folder you are displaying in the address book view. -- Russ Valentine "Tony414" <Tony414@discussions.microsoft.com> wrote in message news:AE3B3E10-8FCD-4840-8924-5B33D99A1F25@mi...

Evaluate a Range
I would like to create a formula that looks at a range with a specified criterion and when it finds the first entry in that range that meets that criterion returns the position of that data in the range. For example, given the following data beginning in A1 and extending to D1: 5,7 ,8,11 I would like to create a formula that looks for a value greater than 10 in that range and then returns the number 4 to signify that it is the fourth entry in that range. Thanks as always for your help. --- Message posted from http://www.ExcelForum.com/ One way: Array enter (CTRL-SHIFT-ENTER or CMD-RET...

Return Range Based on Cell Value
This topic has been covered in varying degrees, but the problem is that I do not want to sum or count the range. I want the range itself returned as the value. For instance in A1 is 7. i.e. July In B1-B12 are dates 201101, 201102, etc. I need a formula that will count out the cells and return the range based on that value in A1. In this case it would return B1:B7. This of course would be nested in another formula. On Tue, 4 Oct 2011 17:18:20 -0700 (PDT), Pablo < > wrote: >This topic has been covered in varying degrees, but the problem is >that I do not want to sum or count the...

Bug: printing of a specific page range is broken in MFC-VS2005
If you create an MFC application that includes printing support, and your application does print-time pagination as described in this MSDN library topic: http://msdn2.microsoft.com/en-us/library/w7wzay73(VS.80).aspx then specifying printing of a specific range of pages in the print dialog does not work. The application always prints all pages of the document. To see this problem download and build the WordPad sample MFC application. Start a new blank document, then paste into it enough text to fill several pages. Choose Print from the File menu. Choose a print range of page 1 to page 1....

CFileDialog multiple file selection
I am using VC++ 6.0 I am trying to allow my users to select at most 50 files using CFileDialog When I over 18 files, the CFileDialog return IDCANCEL. I call CommDlgExtendedError() and it return #define FNERR_BUFFERTOOSMALL 0x300 I then check the first two bytes in lpstrFile and they are 24 and 1; which I take to mean 24*256 + 1 = 6145 The problem is, I have allocated a buffer of 20000 bytes. Can anyone tell me why this is failing I have included my code below Thanks Phi // Code Sampl TCHAR buf[20000] memset((void*)buf,0,20000) CFileDialog fldlg(TRUE,"All Files (*.*)|...

Multiple time ranges
I am trying to create a formula that will look up the time values and return a letter. Times are in column F. Must include seconds (as values does include seconds). 07:00:00 - 17:30:00 = "A" 17:31:00 - 00:30:00 = "B" 00:31:00 - 06:59:00 = "C" <as values does include seconds).> But you miss out whole minutes in your specications. Anyway, this should work, but do check your threshold values. Maybe you need to add a second or less than a second. Set up a table like this (in this example: A1:B4) 0:00:00 B 0:30:00 C 7:00:00 A 17:30:00 B With your time to l...

Selecting Maximum Values in a Query with joins to other tables
I'm having difficulty with a query and hope someone can help me out. Basically, I'm looking to select the maximum amounts from a table based on a column in one of the tables named StateID. It works fine when I do this: SELECT TOP (100) PERCENT dbo.Bids.StateID, MAX(DISTINCT dbo.Bids.Amount) AS Amount, dbo.States.StateName FROM dbo.Bids INNER JOIN dbo.States ON dbo.Bids.StateID = dbo.States.StateID GROUP BY dbo.Bids.StateID, dbo.States.StateName ORDER BY dbo.States.StateName However, when I start to join fields from other tables in...

how to define range names
how do name cells so that I can see what the formula is calculating? Such as A1 * B1 = Final Price where A1 is price and B1 is discount so it reads in the final price cell as price * discount thanks in advance, A One quick way is to use the namebox (the droplist box just to the left of the "=" sign) In Sheet1, say: Select A1, then click inside the namebox, and type the name: price, then press ENTER Repeat to name cell B1 as: discount Then we could put in say C1: = price*discount The other (pe...

Outlook 2003 does not export all fields to any of the selected file formats
Outlook 2003 does not export all fields, it is missing the date and time field for messages. If there is a way please let me know. For what purpose are you exporting your .pst items? --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. Due to the SWEN virus, all mail sent to my personal account will be deleted without reading. After searching google.groups.com and finding no answer, Great Eyes asked: | Outlook 2003 does not export all fields, it is missing the date and | time field for messages. | If there is a way please let me know. "...

Count records b/w time range
I have a sheet which has data with date & time fields like this 19/10/2005 10:10 19/10/2005 11:30 19/10/2005 12:12 19/10/2005 12:15 19/10/2005 10:12 How do I make a formula to pull out count between each half hour slot? like: 10:00 - 10:30 =2 10:30 - 11:00 =0 11:00 - 11:30 =1 11:30 - 12:00 =0 12:00 - 12:30 =2 Please help. Cheers! Sunny =sumproduct(--(B1:B100>--"10:00:00"),--(B1:B100<=--"10:30:00")) -- HTH RP (remove nothere from the email address if mailing direct) <sunilkeswani@gmail.com> wrote in message news:1131569006.191782.216510@g44g200...

How to get weekly open high low close from a range of data?
Hello, I've set up a spreadsheet to download mutual fund prices daily from a certain website and have stored up quite an amount of data already. Now I want to use this data to produce weekly candlestick charts, so I need to get the weekly open, high, low, close from the data. The easiest way I can think of in general is to use a GROUP BY query to extract all such data from the data range, but I don't know how to set up such a query in Excel. Anyone out there can tell me? Thank you very much. Herbert if you have daily dates in column a, and the open, hi, lo, close i columns b-e...

Listbox selected freezes the form
I'm using the following line in the form_open to select the first item in the list which susequently selects data in the second listbox: Me.lstDate.Selected(0) = True However the whole form freezes and nothing works? Any ideas? Many thanks ShariS "ShariS" <u40676@uwe> wrote in message news:7e4b3831704cd@uwe... > I'm using the following line in the form_open to select the first item in > the > list which susequently selects data in the second listbox: > > Me.lstDate.Selected(0) = True > > However the whole form freezes and nothing works? Any i...

Selective enabling of add ons
It would be nice if IE would allow me to enable add-ons for certain web pages. For example, I need flash installed to access my Bank. But I get annoyed by all the flash-based ads on other websites. I would like to enable flash for my Bank & youtube only. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and th...

selected item in listbox turn red instead of black
How can I make the back color of the selected item in a listbox turn red instead of black when clicked? Thanks -- deb Is there a shorter version. That is a bit of overkill for what I need. I just want the selected item to turn red instead of black. Not each row a different color. Any other suggestions -- deb "Wayne-I-M" wrote: > Hi Deb > > Check the link > > http://www.lebans.com/listboxenhanced.htm > > Good luck > > -- > Wayne > Manchester, England. > > > > "deb" wrote: > > > > > How can ...

Using labels to select records
I have several labels on my main page, that are used as buttons. They all four digit names i.e "2279" corresponding to different truck numbers When they are clicked they all open the same form. At the top of this form is a textbox, and I would like it to display the number of the truck selected. The second form contains different buttons containing types of equipment on the trucks. When I click on these buttons I would like the third form only to display the information for the type of equipment (I have a separate table for all the equipment) and the truck selected on the firs...

Code places sum at bottom, require it on top of range
I have a =sum that needs to be inserted one cell above the range (number of rows will vary but always in column "E"). The code below inserts the code one cell below the range but I'm not having any luck figuring out how to change it so that it inserts it one cell above. Any ideas? 1 2 3 4 -10- but I require -10- 1 2 3 4 Worksheets("SecondSheet").Cells(Cells(Rows.Count, 1).End(xlUp).Row, 5).Select x = Range(ActiveCell, ActiveCell.End(xlDown)).Count ActiveCell.End(xlDown).Offset(1, 0).FormulaR1C1 = "=sum(R[-" & x & "]C:R[-1]C)" Dim iLa...

Embeded IF
Using Excel 3. I am trying to assign ABC codes to long list of products based on % of sales. A10 is part #, B10 is %. In C10, need formula to pick a code from following table. The table changes periodically. Code A=over 80% Code B=between 60 and 80% Code C=between 40...60% Code D = less than 40% -- Emily >The table changes periodically. Create a 2 column table like this... 0%...D 40%...C 60%...B 80%...A Assume the table is in the range J2:K5 Then...this formula entered in C10 and copied down: =LOOKUP(B10,J$2:K$5) -- Biff Microsoft Excel MVP ...

Problem Working with Named Ranges
Hi, On one worksheet in my workbook, I have an outline with expandable and collapsable rows (using the group function). In another worksheet, I have different buttons which runs different macros that will link me to the sections of the outline that I want expanded (while the rest remains collapsed). I was able to get a macro to work using absolute values for a cell position, but I have been unsuccesful trying to work with name ranges. This is a problem, as I frequently add new rows to the outline so I'm constantly having to update the macro. Can someone help me put relative v...

How do I set the range of a trend line in Excel?
I am using excel to create trend lines in a chart. I have four different sets of data, two sets lie on weeks 1-6, and two sets lie on weeks 7-12 (x-axis). Right now, I can make individual trend lines for all four sets of data, but they extend the length of the x-axis (all 12 weeks). How do I get the trend lines to extend just within the data range? Thanks for any help. In a line chart (in any chart with a category axis, in fact), a trendline covers the entire range of the X axis. If you're able to plot the points on a Scatter chart, the trendline only spans the actual range of ...

Sycronizing Ranges
sIn A1.C10 I have data imported (via MS Query) from an external source. In D1.F50 I have cells that refer to A1.C10. My problem is that if my import expands from A1.C10 to (say) A1.C20 the range D1.F50 is also shifted when I want it to remain fixed. How can I fix D1.F50 in place so it picks up the right row in A.C whatever they expand to. Many thanks -- Peter London, UK ...