Free Microsoft Excel 2013 Quick Reference

Non contiguous cells Results

I know this has to be real simple, but I can't find an answer.

I have a column ("A") of data on a sheet ("OldFiles") and I just need to
know how many cells are in that column have data in them. The column is
sorted, so the non-blank cells will be contiguous and will be at the top of
the column.



I am creating a chart and want to be able to change the yvalues depending
upon criteria selected in a dropdown box.

I have a problem with non-contiguous cells.

In my worksheets I want the yvals to be 6 values based upon non-conitguous
data e.g.

Cells(1,5) Cells(3, 5) Cells(5,7), Cells(7,7) Cells (9,7), Cells(11,7)

I need to use the Cells( row, column) format as opposed to Range("E1") for

When using non-contiguous cells this will work...

Set yvals = Worksheets("Sheet 1").Range(Cells(1,5), Cells(5,5))
ActiveChart.SeriesCollection(1).Values = yvals

How do i do a similar thing but allow a refernce to non-coniguous cells as

I'd be both grateful and interested in learning how this can be done. Any
help would be warmly appreciated.

By the way, I am using Excel 97...



I guess I stumped everyone in excel.worksheetfunctions group 'cause no
one answered July 28 post. Hope someone here has the answer.

A spreadsheet displays the #Value error in many cells. Each of these
cells contains a formula that simply sums 3 non-contiguous cells in the
same column e.g. =SUM(B16,B14,B12). The formula toolbar indicates the
reason for the error is that a cell or cells in the range contains a
constant which cannot be evaluated. However, no data is visible in the
referenced cells (or formula bar when they are clicked on). There are
no named ranges that I can see. If I select each of the cells one at a
time and press delete then undo the deletion, no change in the formula
result (#Value) is observed. However, if I select the first two cells
referenced by the formula and press delete, the formula result becomes
0. The #Value error is still displayed if I undo those deletions and
select cell 2 and 3 and click delete. It seems the constant(s) somehow
relates to the combination of cells 2 and 3 (or they each contain a
constant). One other strange looking aspect is that the cells seem to
have a number format that shows Custom in the list of formats and
Custom_ (yes, that's an underscore) for the custom format. However, one
cell on the sheet has the same format and it's formula result is 0, so
I don't think it's a formatting thing. For the life of me, I cannot
figure out the #Value error when nothing shows in the formula bar or
referenced cells. Does anyone have any clue how a cell could contain a
constant that can't be seen?

Okay, here's one that I can't seem to figure out. I have a sheet that people rank order a list 1-6. I have assigned named ranges for 1st, 2nd, 3rd, 4th, 5th & 6th. the list would look like this:

Rank A C
1 Item 1 Item 4
2 Item 2 Item 2
3 Item 3 Item 5
4 Item 4 Item 6
5 Item 5 Item 1
6 Item 6 Item 3

So, A1:Z1 would be named "FirstChoice", A2:Z2 would be named "SecondChoice", etc.

If you notice there is a gap between the lists. Is this going to effect the use of the named range in the following formula:

=COUNTIF(FirstChoice,"Item 1")

What I'm looking for is in the table above to return a value of 1. The same formula would be in a cell but would be changed for Item 4. It should return a value of 1 as well.

I hope I have described this well enough.


I am trying to find a way to count the number of cells that are conditionally formatted with a certain fill colour.
I have searched the web and found several posts that say how tricky it can be and and some that suggest using a function that recreates the formula of the conditional formatting. but sadly, I do not know how to replicate easily the formula used, and the code i found to count the Conditional formats does not appear to work. I believe it is because of the type of conditional format I have selected.
So can any one help? here is what I am trying to do.

I have a quite large and complex spreadsheet that has lots of data, amongst this are 4 columns containing user ID's (A user can have a Main identifier and up to 3 alternate Identifiers). But I need to check for Duplicate user ID's across all 4 ID columns eg Does and ID exist in Col H, or Col K or Col M or Col T? if so colour all the duplicate cells in red.
Now Excel 2007's conditional formatting has let me do this very easily, because it has a built in function that allows me to format "only Unique or Duplicate Values" and it applies to the "Range selected". So I can select the non contiguous columns of H, K, M and T and select a fill colour of Red, and this works perfectly.

However, I need to count those coloured cells. As I said right at the beginning, I do not know how i could construct the formula to be the same as the conditional formatting.
The code I have found on other sites does not work on the cells Conditionally formatted in this way (its does work with cells that are conditionally formatted in a far simpler way, so I am sure it is not me using the code wrong)

Any help would be greatly appreciated

First off, I didn't really know how to search this so if the thread already exists I apologize. I've looked into non-contiguous functions but I'm a little confused and not sure if its what I need, or if what I want to do is even possible. I know protocol is to include sample data, but I'm going to see if this is answerable before I figure out how to do that. Sorry slash thanks.

I have a large table of financial data that I'm trying to shave a few columns off of. I have 3 identical columns used in calculating 3 different IRRs. At the bottom of each row, below the table, I have a vlookup that brings in the latest value from 3 separate columns as part of the IRR calculation. What I would essentially like to do is have only one column, and write 3 XIRR functions that use the data from the column, and also includie one cell from a different column. If that makes sense...

One calculation is currently: M1104 = XIRR(M4:M1101, A4:A1101)
M:1101 is the vlookup that is currently displaying the data in J1100
Optimal solution would be something that says M1104 = XIRR(M4:M1100-and-then-J1100, A4:A1101)

As I said before, I know sample data will probably be necessary, but I wanted to see if something like this was even possible first.

Thanks in advance

Using Excel Office 2003, I'm being asked to create charts from existing
worksheets, often several within a workbook, using specific cells
(total/average cells). The cells are not adjacent to one another, but they
are identical from one worksheet to another. In my case, the cells are K15,
K29, K42, W15, W29, and W42. I'd like a separate chart for each worksheet,
and would like to have the chart automatically created once the data is in
the worksheet. Is this possible?

Hi All,
I've got a spreadhseet which has 9 non-contiguous variables which need to be 'scored' and then summed. For each of A, C, E, G, I, K, M, O, Q, responses can range from 1 to 4 (although some are missing) (see attached spreadsheet). I want to be able to score each response based on the following criteria:

if answer=1 or 2, then score = 1
if asnwer=3, then score = -1
if answer=4, then score=0
if answer is missing, then score= MISSING

Using this scoring protocol, scores can range from -9 to +9. As you will see in the atached spreadsheet, some cells are missing one or two scores and other rows are missing altogether.

Here is my problem. I want to be able to score each cell (see the 'ifs' above) and then sum across the non-contiguous columns to compute a final score WITHOUT counting the blanks as zeros. In other words, how can I add or SUM items up without Excel putting a darn 'zero' in the rows where there is no data?

Hope I've explained this okay and thanks in advance for your help.


I'm really struggling with Excel 2007 conditional formatting - I like the new features, but am very frustrated with how it is applied.

In earlier versions of Excel, I could select a single cell (or subgroup of cells) within a range that had conditional formatting, and remove it from that single cell. But it seems in Excel 2007 that if I remove conditional formatting from that one cell, it deletes it from the entire range as well.

For example, just say range A1:D10 has conditional formatting that colors the cell fill green if the value is 1. And then if I select cell C3 and try to remove the conditional formatting from that one cell by deleting the rule, the conditional formatting is removed from the entire range A1:D10, not just C3. I can get around this by going into the Applies To field and changing the range by manually selecting 4 areas surrounding that one cell. But what if I have multiple rules, or multiple non-contiguous cells from which I want to remove the conditional formatting - in earlier versions I could simply select the cells and delete the conditional formatting rules from them, but the process in Excel 2007 seems so much more complex.

And a related problem - just say I have complex conditional formatting applied to the same range (A1:D10) involving 5 rules. If I want to copy this formatting and paste it to another range (eg F1:G10) it does so. But if I want to make a slight change to the conditional formatting in the new range (F1:G10) like changing one number in the conditional format formula, the change affects both the original range and the new range. If I want the minor change to apply only to the new range, I have to change the Applies To range of the original range, and add the 5 rules manually again to the new range.

This is driving me crazy. Am I missing something? Is there a way to copy, paste and edit conditional formatting in a similar way to previous versions of Excel?


I have a workbook witch contains 25 rows of data every 7 rows. The data i'm looking for starts in C4 ends at C28, then re-begins at C36 down to C60 and so on with a pause of 7 rows between range.
The ranges are:
• C4:C28
• C36:C60
• C68:C92
• C100:C124
• C132:C156
• C164:C188

The above are maximum ranges a sheet can have (based on my layout in the book). Some sheet may have only the first two ranges while other can have only C4:C28. It varies from sheet to sheet.

within each 25-row data I have:
-- col. C [names]
-- col. D [5-digit number] formatted as "00"# (it cannot be any other format)
The 25-row range can be contiguous or non-contiguous.

I need a macro that is able to select and copy the cells that contain data in col. C but also contains the code in the next cell from the right. So, if I had in C5 a name and D5 was empty then that name should be excluded.

In the sample below, the only data that should be copied is cell C5 and C10 because they were the only ones that had data in column D.

Thanks fo reading and hoping for some good replies.

I know IRR works with non-continguous values, but I can't
get XIRR to work. Here's what I'm doing:

=XIRR((B1,B5), (A1,A5), 0.07)

1/21/2005 -750
12/31/2005 795

I get #VALUE! IRR works on the same cells, minus
the date arguments of course.


I have non contiguous cells with numeric values in them. I want to multiply each cell by the next numeric cell found. I have at least 10 sets of data with a cell containing text (part id) followed by a cell containing a number (qty).

I need something similar to SUMIF but instead of adding, I want to multiply the values in my range....

Any ideas?

I have been trying to do this for quite some time.

I have found that:

Set LastCol =
Range("XFD" & ActiveCell.Row).End(xlToLeft)
    Application.Goto Range(ActiveCell, LastCol)
will select active cell to last used cell in the same row. I have tried modifying this to
select a range of the active cell to the last used cell in the same column, non contiguous, but can't seem to figure it out.

Any tips are greatly appreciated.

It's obviously not as simple as:

Set LastRowA =
Range(ActiveCell.column & " 1048576").End(xlUp)
    Application.Goto Range(ActiveCell, LastRowA)
I am using excel 2010 and I know 1048576 probably isn't the best way to try this but it's the best I could figure out.

Hello All,

have Sheet("List") with an autofilter and sheet("Table") with pivot table. How do I base the pivottable on the filtered (visible) items from "List"?

I use this code so far but:

Dim FilteredRange As Range
Dim MyFilteredRange As Range
Set FilteredRange = Sheet1.AutoFilter.Range. _
MsgBox FilteredRange.Address, vbOKOnly, "Address"
Set MyFilteredRange = Sheets("List").Range(FilteredRange.Address)
'MsgBox MyFilteredRange.Address
ThisWorkbook.Names("MyTable").RefersTo = MyFilteredRange.Address
Dim Worksheet As Worksheet
Set Worksheet = ThisWorkbook.Worksheets("Table")
For Each PivotTable In Worksheet.PivotTables
    With PivotTable
    End With
Next PivotTable
Code breaks on the pivot table is refreshed with "Reference not Valid"

so I check the name, and the Refers to field is filled up with this
How do I construct a proper range reference based on the visual cells?


I think I've discovered a limitation of COUNTIF but I need confirmation from the experts on this board as well as any ideas for a workaround. In a nutshell, I'm trying to use COUNTIF with a named range that consists of non-contiguous cells.

For example, I have the following named range called P1Q1 defined in Sheet1:


As you can see this range is comprised of cells that are located in a single row but multiple, separated columns. I want to use the COUNTIF function to analyze that range and count the number of times an "X" appears. Here is the function I'm using:


I'm receiving this error:

"The value used in the formula is of the wrong data type."

I've seen dozens of examples out there that use a named range as the range argument in a COUNTIF function but in all of those examples the named range was a group of contiguous cells. I would not expect Excel to have trouble with this but it clearly does.

Anyone have any ideas for a workaround on this? Thanks in advance!

I have an Excel 2007 file. It has a sheet with about 110,000 rows. I filter by one column and this reduces the view to 8777 rows. I then select all the visible rows in one column, copy them and paste them into a text document.
I did this a month ago without any error messages and I just checked all expected rows are in my text document. However, today I used exactly the same Excel file, same worksheet, same filter and selected the same rows to copy, but this time I got a message like this:
"Microsoft Office Excel cannot create or use the data range reference because it is too complex."
I did a little digging with Google and find that there is a limit on the number of non-contiguous rows you can select. It is 8192. Fair enough. However, I am perplexed as to how I successfully managed to do this a month ago on the same data without getting any messages. I know I did it in one operation (rather than, say, copy/pasting the first 5000 and then the other rows). Any ideas what could have changed? It is driving me nuts.



Hello everyone,

I want to apply 2 changes to an Excel spreadsheet, in this specific order, and despite hard search here and in Google, I haven't found a solution yet:

1. The first thing I want to do is to order certain rows values (numbers, in this case), i. e. order the cells horizontally, from left to right. I already found a simple solution like this one ( ), but this only selects one row each time, and I want to apply it simultaneously to dozens or even hundreds of rows, that aren't sequential. The layout of the rows I want to sort the spreadsheet is something like the 8th row, then the 12th, and then a pattern appears (the 15th, 18th, 21st, (...) rows or, simply put, every 3rd row beginning from the 12th). Regarding selecting the "nth" row, I found this:

So, I think a macro is the only way out of this. I found this thread ( ), but it's intended to sort multiple contiguous rows, so it's a different case.

Using the code from that thread, I would be starting from this:

Dim RgToSort As Range
Dim RgRow As Range
Application.ScreenUpdating = False

Set RgToSort = Range(Range("C1:I1"), Range("C1:I1").End(xlDown))

For Each RgRow In RgToSort.Rows
    RgRow.Sort Key1:=Range(RgRow.Item(1).Address), Order1:=xlAscending, Orientation:=xlLeftToRight, OrderCustom:=1
Application.ScreenUpdating = True
End Sub
2. Concerning the same range of cells mentioned in point 1, I want to change their values. The original values are integers, ranging from 1 to 8, for instance, and I want to keep 1 and 2, and change 3 to 1, 4 to 2, 5 to 1, 6 to 2, 7 to 1 and finally 8 to 2. I have been advised to use the VLOOKUP function ( ), but
it seems (again) that this only applies to contiguous rows. Am I wrong?

I don't know if I'm being sufficiently clear explaining myself. By the way, I'm using Excel 2007.

If anyone could help me, I would be deeply appreciated.


Hi all,
This is my first time posting here, thanks in advance for the help.

I have defined several ranges and want to loop through using a For statement. Here is my current code (basically I want to identify today's date within a calander (the ranges are non-contiguous which is why I have added them seperately):

    Dim Rng1, Rng2, Rng3, Rng4, Rng5, Rng6, Rng7, Rng8, Rng9, Rng10, Rng11, Rng12, CalRng As Range 
    Dim I As Integer 
    Set Rng1 = Range("Jan") 
    Set Rng2 = Range("Feb") 
    Set Rng3 = Range("Mar") 
    Set Rng4 = Range("Apr") 
    Set Rng5 = Range("May") 
    Set Rng6 = Range("Jun") 
    Set Rng7 = Range("Jul") 
    Set Rng8 = Range("Aug") 
    Set Rng9 = Range("Sep") 
    Set Rng10 = Range("Oct") 
    Set Rng11 = Range("Nov") 
    Set Rng12 = Range("Dec") 
    For I = 1 To Rng1.Cells.Count 
        If Rng1(I).Value = Date Then 
            Rng1(I).Interior.ColorIndex = 6 
            Rng1(I).Interior.ColorIndex = 0 
        End If 
    Next I 
    For I = 1 To Rng2.Cells.Count 
        If Rng2(I).Value = Date Then 
            Rng2(I).Interior.ColorIndex = 6 
            Rng2(I).Interior.ColorIndex = 0 
        End If 
    Next I 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Is it possible to have something instead like:

    Dim Rng1, Rng2, Rng3, Rng4, Rng5, Rng6, Rng7, Rng8, Rng9, Rng10, Rng11, Rng12, CalRng As Range 
    Dim I As Integer 
    Dim J As Integer 
    Set Rng1 = Range("Jan") 
    Set Rng2 = Range("Feb") 
    Set Rng3 = Range("Mar") 
    Set Rng4 = Range("Apr") 
    Set Rng5 = Range("May") 
    Set Rng6 = Range("Jun") 
    Set Rng7 = Range("Jul") 
    Set Rng8 = Range("Aug") 
    Set Rng9 = Range("Sep") 
    Set Rng10 = Range("Oct") 
    Set Rng11 = Range("Nov") 
    Set Rng12 = Range("Dec") 
    For J = 1 To 12 
        For I = 1 To Rng1.Cells.Count 
            If Rng(J)(I).Value = Date Then 
                Rng(J)(I).Interior.ColorIndex = 6 
                Rng(J)(I).Interior.ColorIndex = 0 
            End If 
        Next I 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Thanks very much in advance.

I have following code inside a module:

    With Range("B4:B11,D4:D11,F4:F11").Borders(xlEdgeRight) 
        .LineStyle = xlContinuous 
        .Weight = xlThin 
        .ColorIndex = xlAutomatic 
    End With 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
It works fine if called from a Button, another procedure or if debuging(F8).

Here is the problem:
If I call the procedure from the "Before_Print" event,

End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
it pops up an error in the With line.


If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

Has anybody an idea what is wrong with the code; why is it behaving like that?
I appreciate your help very much.


I am writing two subs and one function so that I can store a non-contiguous range of values from a spreadsheet. These are stored in an array.
Then, I would like to sort through that array to check for unique values. Once I have the unique values, I would like to copy them to a range of cells.
I have the data successfully stored in the array as follows:

However, when I run the UniqueItems Function, I get the following result, which does not seem to identify the values as unique.

At the end of the uniqueitem functions I receive the error message "object variable or with block variable not set"

Any help with sorting this out will be immensely helpful as I am working under a deadline.

     '   Accepts an array or range as input
     '   If Count = True or is missing, the function returns the number
     '   of unique elements
     '   If Count = False, the function returns a variant array of unique
     '   elements
    Dim Unique() As Variant ' array that holds the unique items
    Dim Element As Variant 
    Dim i As Integer 
    Dim FoundMatch As Boolean 
     '   If 2nd argument is missing, assign default value
    If IsMissing(Count) Then Count = True 
     '   Counter for number of unique elements
    NumUnique = 0 
     '   Loop thru the input array
    For Each Element In StoreRange 
        FoundMatch = False 
         '       Has item been added yet?
        For iloop = 1 To NumUnique 
            If Element = Unique(iloop) Then 
                FoundMatch = True 
                Goto AddItem '(Exit For-Next loop)
            End If 
            Debug.Print Unique(iloop) + "iloop data" 
        Next iloop 
         '       If not in list, add the item to unique list
        If Not FoundMatch Then 
            NumUnique = NumUnique + 1 
            Redim Preserve Unique(NumUnique) 
            Unique(NumUnique) = Element 
        End If 
        Debug.Print Element + "unique data" 
    Next Element 
     '   Assign a value to the function
    If Count Then UniqueItems = NumUnique Else UniqueItems = Unique 
End Function 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

    Dim MyArray As Range 
    MyArray = UniqueItems(ActiveSheet.Range("E8,E17,E26,E35,E44"), False) 
    Range("E57:E59").Value = Application.WorksheetFunction.Transpose(MyArray) 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

No luck finding an answer? You could always try Google.