Union and Intersect range... but any SUBTRACT?

Hi all,

I see the functions to union and intersect a range, but I don't see a 
function to subtract one range from another.

Does anyone know if this function exists, or if there is a VBA function out 
there to do this I can paste into my application?

Thanks!

Ray 


0
Raeldor (2)
11/1/2004 4:16:03 PM
excel 39879 articles. 2 followers. Follow

2 Replies
521 Views

Similar Articles

[PageSpeed] 2

Hi Ray,
Found in a search of newsgroup archives
    http://groups.google.com/advanced_group_search?q=group:*Excel*&num=100
    search all words:   union intersect subtract

Tom Ogilvy posted a SubtractRange subroutine in a
     reply 2000-08-06 that should work for you.
  http://google.com/groups?threadm=%23OOGqH7%24%24GA.293%40cppssbbsa04
---
HTH,
David McRitchie, Microsoft MVP - Excel    [site changed  Nov. 2001]
My Excel Pages:  http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page:        http://www.mvps.org/dmcritchie/excel/search.htm

"Ray Price" <Raeldor@community.nospam> wrote ...
> I see the functions to union and intersect a range, but I don't see a
> function to subtract one range from another.
>
> Does anyone know if this function exists, or if there is a VBA function out
> there to do this I can paste into my application?


0
dmcritchie (2586)
11/1/2004 5:05:57 PM
Very cool, thank you!

Any chance this will make it into Excel one day?

"David McRitchie" <dmcritchie@msn.com> wrote in message 
news:usnPCVDwEHA.3084@TK2MSFTNGP10.phx.gbl...
> Hi Ray,
> Found in a search of newsgroup archives
>    http://groups.google.com/advanced_group_search?q=group:*Excel*&num=100
>    search all words:   union intersect subtract
>
> Tom Ogilvy posted a SubtractRange subroutine in a
>     reply 2000-08-06 that should work for you.
>  http://google.com/groups?threadm=%23OOGqH7%24%24GA.293%40cppssbbsa04
> ---
> HTH,
> David McRitchie, Microsoft MVP - Excel    [site changed  Nov. 2001]
> My Excel Pages:  http://www.mvps.org/dmcritchie/excel/excel.htm
> Search Page:        http://www.mvps.org/dmcritchie/excel/search.htm
>
> "Ray Price" <Raeldor@community.nospam> wrote ...
>> I see the functions to union and intersect a range, but I don't see a
>> function to subtract one range from another.
>>
>> Does anyone know if this function exists, or if there is a VBA function 
>> out
>> there to do this I can paste into my application?
>
> 


0
Raeldor (2)
11/1/2004 5:13:26 PM
Reply:

Similar Artilces:

How to reset the range name to refer to another cell?
I have set "G" as the a range. In the later part, I need to use "G" as a reference to set itself as another range. what is the correct syntex? Set G = Worksheets(E).Range("B21") .... Set G = Range(G).End(xlRight) <-----Error occurs here Please help! You are confusing the range with its name: Set G = Worksheets(E).Range("B21") Set G = G.End(xlRight) -- Gary''s Student - gsnu201001 "Terry" wrote: > I have set "G" as the a range. > In the later part, I need to use "G" as a ...

Dynamic union query based on "formulas" in table
Hi, I have a table that looks something like this: Company Product Sales 1 A 5 1 B 2 1 C 7 2 A 8 2 B 1 2 C 2 I want a query to produce a table showing Products A though D which is the sum of Products A and B. Result: Company Product Sales 1 A 5 1 B 2 1 C 7 1 D 7 2 A 8 2 B 1 2 C 2 2 D 9 I have many...

Copy to Named Range
I can "Set Range" then "Copy" then "select" then paste as below If Cells(CCC - 1, 1).Value = Cells(CCC, 1).Value Then Set MyRange = Cells(CCC, 100).End(xlToLeft).Offset(0, 1) Range(Cells(CCC - 1, 11), Cells(CCC - 1, 14)).Copy MyRange.Select ActiveSheet.Paste For speed I do not want donot want to select but the following code errors out Range(Cells(CCC - 1, 11), Cells(CCC - 1, 14)).Copy Range(Myrange) -- Message posted via http://www.officekb.com Just off the top of my head, you need quote marks around the range's name in the Range cal...

Sum values over range of dates
Need Help!!!! PLEASE! Here is the example... If I have a huge table of dates and values for those dates and need to sum based on a given date range, how do I sum it up? date 1 date 2 date 3 date 4 date 5 Sate 1 12 7 8 1 1 State 2 10 4 6 2 8 State 3 5 4 2 3 7 start date end date Sum would be??? Sate 1 2 5 17 State 2 1 3 20 State 3 2 4 9 But what is the formula I can use for this??? How can I set it up so I just need to change start and end dates and it will calculate automatically???? Please help! Thank You!!!! Assume: This data is in the range A1:F4. > date 1 date...

Local Range Name & Sheet Duplication
Hi, In my workbook, I have a global variable sheet called "GlobalVars" and individual worksheets. The GlobalVars will have variables that affect all calculation sheets. My variables are localized to this sheet. An individual worksheet represents a region. It will use both specific region inputs and global variables (brought over from GlobalVars) . It performs the region's calculations. Again, all variables are localized. I want to be able to duplicate an individual worksheet as many times as needed. But when I do this, the variables from GlobalVars break. I am left...

Macro maximum range lengths
I am trying to enter code to auto-expand merged cells. I have multiple ranges within the same worksheet that will be affected by this macro. I believe that I have reached the maximum range length for the macro. I need to add more cells. How would I go about adding them to this macro OR can I add a second macro for the additional cells? Here is what I currently have: Private Sub Worksheet_Change(ByVal Target As Range) Dim NewRwHt As Single Dim cWdth As Single, MrgeWdth As Single Dim r As Range, c As Range, cc As Range Dim ma As Range Set r = Range("A18:G18,A19:G19,A2...

Dynamic ranges
I'm doing one thing wrong and I can't see the solution I believe that my problem is defining the x-axis in the dynaminc range. I can get the number to start at the right point - however I'm getting blanks on the right side of the graph - like it thinks there are numbers to graph but there isn't. Does this make enough sense or do you need more detail I think we need more detail. Like what formula are you using for the dynamic range. What content do you have in the cells. You could do a quick check by going to the Names dialog and placing you cursor in the control that...

Subscript out of range error?
I keep getting a subscript out of range error with this macro. Any help would be great. Public Sub AddSheetToEnd() 'Create a new sheet Dim NewWorksheet As Worksheet Set NewWorksheet = _ Applicaiton.Sheets.Add( _ After:=Worksheets(GetLastSheet), _ Type:=XlSheetType.xlWorksheet) ' Rename the worksheet NewWorksheet.Name = "Added Worksheet" ' Place a title in the worksheet. NewWorksheet.Cells(1, 1) = "Sample Data" ' Add some headings. NewWorksheet.Cells(3, 1) = "Lable" NewWorksheet.Cells(3, 2) = "Data" NewWorksheet.C...

simple range problem
what am i doing wrong dim myRange as Range dim iNum as integer iNum= 6 Set myRange = Cells(iNum + 2, 1) myRange.Value = 34 trying to set a cell myRange to cell(8,1) then putting the value 34 into the cell I just tested with xl2002 and it worked fine but why not just cells(inum+2,1)=34 or cells(8,1)=34 -- Don Guillett SalesAid Software donaldb@281.com "cantonarv" <arvindev@gmail.com> wrote in message news:1132661208.982429.309000@g14g2000cwa.googlegroups.com... > what am i doing wrong > > dim myRange as Range > dim iNum as integer > iNum= 6 > > Set m...

Named Range launches VB?
Hi, when I start a brand new workbook, enter some numbers in a column and attempt to name the range by typing the name directly into the Name Box (Price), Excel launches VB and prompts for a password to access the VB file. If I use any other name for the range, it works fine. If I create the named range (Price) in Insert-->Name-->Define... it also works. But, why when directly typing Price into the Name box, does it launch VB? Thanks, please post replies here only. Brian in NY Typing a name in the Name Box and pressing Enter does a GoTo if the name exists in the workbook or, s...

Sum by range of time
Hello, I have a column of values (A) and a column of times (B). The times fall between 8:00am and 10:00am, but I'm only interested in the values between 9:00am and 10:00am. I want to sum all values in column A whose time values in column B are >=9:00 and <=9:59. I've tried sumif, nested if, or() and and() functions, and I can't get anything to work properly. Can anyone help? Thanks! Hi One way =SUMPRODUCT(($B$2:$B$100>=TIME(9,0,0))*$B$2:$B$100<=TIME(9,59,0))*$A$2:$A$100) -- Regards Roger Govier "rocketD" <darahx@gmail.com> w...

duplicate data in range clear contents
Hi All, I have a range which V12:V18, data consistantly changes and there might be some duplications. I would like to find the duplicated data and just clear the duplications without deleting any row. Because those rows effecting other formulas.Is that possible? Thanks \\ Baha baha17@gmail.com;562078 Wrote: > Hi All, > I have a range which V12:V18, data consistantly changes and there > might be some duplications. I would like to find the duplicated data > and just clear the duplications without deleting any row. Because > those rows effecting other formulas....

Superformula required for looking up Duplicates in a range
Hi I've been trying to construct a Duplicate entries/Lookup superformula all to no avail. Column A is a helper range (optional). Column D contains strings of numbers and/or characters. I wish to search only Column D for the occurrences of "2_34a". What I would like is a formula to list how many cells back each duplicate occurred from each other. My worksheet setup: A2 B2 C2 D2 E2 1 2_115 2 __dd3 3 2_34a 4 x_21_ 5 _1xx_ 6 54321 7 _4_1_ 8 54321 9 2_34a 10 54321 11 54321 12 54321 13...

deselect cells within a range of cells
I have found this to be most frustrating!!! Windows Explorer allows you to select an entire list of items and then deselect individual items by holding the ctrl key down and clicking the items you wish to deselect. Why does this not work in Excel??????? I have a list of 100 items and wish to deselect about 10 that are randomly dispersed in the selection. This seems to such a simple thing, but I've found no solution. Try this: =INDIRECT("E"&C1) -- HTH, RD ============================================== Please keep all correspondence within the Group, so all m...

Excel 2000: allow users to edit ranges
I have Excel 2002 SP3 but another user has 2000 SP3. I created a big worksheet but only want him to be able to edit 1 column, so I used the Tools > Protection > Allow users to edit ranges and gave him access to that 1 column. When he opens it in 2000, he can not edit anything without first removing the protection from the entire worksheet. When I go to his Tools > Protection, he only has 3 options: protect sheet; protect workbook; protect and share workbook. He has no allow users to edit ranges option. Was this option not included in Excel 2000? Is there an update or add in f...

external (not remote) data ranges using defined names
I have created a Source file containing information that I want to access from multiple Destination files. From time to time I will need to add rows or columns to the Source file and have this reflected in the Destination workbooks. I am using Validation lists and Defined names to do this and it works OK. As long as the two workbooks are open any changes to the Source file are reflected in the Destination file. But if the Destination is closed while the Source file is being changed, when the Destination is open the reference in the Defined Name has not changed even if the program ask...

Delet data from a field using a timer based on a date range
I would like to setup a code that would delete data from a table based on a date range entered. Here is the scenerio, I have training database. Two fields one for training hire date and the other trainer, I would like to put in a time that says 90 days from hire date the trainer's name is deleted from the trainer field. Is this possible. Please advise. It's easy to create a query that can do that, but there's no real way to make Access run the query automatically. -- Doug Steele, Microsoft Access MVP http://www.AccessMVP.com/DJSteele (no e-mails, please!) ...

Current Time subtract a specific amount every second
I have a total in A1 and B2 I want B2 to be subtracted from A1 every second of the day.. Based on the Current Time in D4 D4 contains =now() Is this what you want: =a1-b2*mod(d4,1)*86400 Format as number Regards, Fred "Jman" <Jman@discussions.microsoft.com> wrote in message news:E1759EFC-38AF-443D-A72A-8ED946580876@microsoft.com... >I have a total in A1 and B2 > > I want B2 to be subtracted from A1 every second of the day.. Based on the > Current Time in D4 > D4 contains =now() > "Fred Smith" wrote: > Is thi...

Copying number to clipboard, subtracting 398 then pasting the value to overwrite the original
Hi. I am very new to this. I'd be really grateful if someone could help/guide me. I want to create a macro in Microsoft Word but I don't know visual basic. I want to be able to highlight a number then: - copy it to the clipboard - subtract 298 - paste the value to the Word document, overwriting the original text Hi John, There is no need to involve the clipboard if you are only changing the selected number. The following macro subtracts 298 from the selected number. Sub Subtract298() If IsNumeric(Selection.Text) Then Selection.Text = Val(Selecti...

Order By and unions
I have the following statement which puts all the rows that have a length of 14 first and all other next. But I want to sort the top select in ascending order and the bottom half in descending order. I know I can't do it directly as the ORDER BY statement needs to go after the last select. I also thought about setting up 2 views each with an order statement. But is there a way to just change this statement and do the same? SELECT 0,FormatDescription FROM Forms WHERE LEN(FormatDescription) = 14 UNION SELECT 1,FormatDescription FROM Forms WHERE EN(FormatDescription) <...

Pivot Table view of Union Query
Dear All, I can see what I need at the Pivot Table view of a Union Query. However, this cannot be correctly exported to Excel. Although there is a function of "Export to Microsoft Excel", however, all "detail data" in Access was gone - leaving only the count of the data in Excel. How can I show all these detail data in the data area of the Pivot Table? Alternatively, is it possible to copy the Pivot Table in Access and then paste in Excel? I cannot see any Select Rows commands in the pull down menu. Thank you very much. Hong -- Message posted via AccessMonster.com h...

Subscript Out of Range
Hi, I cannot export one of my reports in a project to Excel. It says: "Subscript out of range". Export to rtf format is OK. What could be a reason of such behavior? Thanks in advance, AK ...

Lookup / Array Range / find the value
I have seen similiar examples but not that were not using exact matches to retrieve data. This seems simple and maybe I am over thinking but I have been unable to crack this nut. I have a table where the first tier sales runs between 0 and 20k and awards 1K commission. I want to be able to enter the salse result for various team members and obtain the correct commission amount based on their sales for the period. How should I complete the commission formula for this process? Sales Value Start Sales Value Stop Commission $0 $20,000 $1,000.00 $...

Excel find and replace within a range of values?
I've basically got a data set, that I need to convert to values for a neural net. My problem is I can replace standard entries such as 'male' to the corresponding numeric value but for fields such as age, where say a range between 25 and 35 is 0.5, it has to find every age within that range and convert them all to 0.5. Is there an easy way of doing this without having to do it manually? Si -- Bumble "I'm flat out, you're so beautiful to look at when you cry. Freeze don't move, you've been chosen as an extra in the movie adaptation of the sequel to your life...

Sum values over range of dates
Need Help!!!! Here is the example... If I have a huge table of dates and values for those dates and need to sum based on a given range, how do I sum it up? date 1 date 2 date 3 date 4 date 5 Sate 1 12 7 8 1 1 State 2 10 4 6 2 8 State 3 5 4 2 3 7 start date end date Sum would be??? Sate 1 2 5 17 State 2 1 3 20 State 3 2 4 9 But what is the formula I can use for this??? Please help! You first use a union query to normalize your table/spreadsheet. SELECT State, 1 as TheDate, [Date 1] as TheValue FROM tblOfDates UNION ALL SELECT State, 2, [Date 2] FROM tblOfDates UNION ALL SELECT Sta...