1. Smokeyham

    Designing a spreadsheet to show the most up to date entries - 4 Different Offices

    Hello, I am keeping track of our emergency supplies across four different offices. The supplies kept at the offices are all the same, namely: Gloves - Small Gloves - Medium Gloves - Large Gloves - XLarge N95 Masks Four different people will be sending me reports on different days (they have...
  2. M

    How to count rows in non-contiguous range

    I need to count the number of rows in a non-contiguous range, such as: “C13, D17:E18, F22, G25, C29:F33” If I select those cells on my worksheet using Ctrl and the left mouse button, and then run the following code: Dim Rng As Range Set Rng = Selection Debug.Print Rng.Rows.Count...
  3. C

    Formula to test if multiple non-contiguous cells are equal in value in the cells?

    Hi, I am trying to get a formula to test if multiple non-contiguous cells are equal in what the cell has in it. I've seen many ways to do this for contiguous cell ranges but not non-contiguous cells. For example I am trying to see if B4,D4,F4,H4 are all equal in their cell contents. Thanks...
  4. E

    Find First Blank in Non-Contiguous Range - VBA

    I 'm trying to find the first blank cell in a non-contiguous range (see code below). When all five cells are blank, "C4" is assigned to variable x. "C2" is the expected and correct value. Any idea why that's happening? No doubt I'm doing something wrong. Set x =...
  5. S

    How to calculate IRR of multiple projects

    I would like to calculate the IRR of multiple different projects, I try to take array of cashflow and dates of different projects, then using XIRR, but XIRR can only calculate continuous data. Below is a simplify sample, I can manually put all the data of one project together, but I can't do...
  6. K

    Creating a sparkline using a non-contiguous range

    Hello, trying to put together a sparkline using a a non-contiguous range and getting errors, any ideas on how to achieve this?
  7. auto.pilot

    Seeking method to copy non-contiguous columns with LastRow range method

    As the title says, I am seeking to copy a non-contiguous range of cells in columns that are identified by code using Lr (LastRow). The following works, because only the range of B6:C37 and H6:H37 are copied. Sub CopyNonContRange() Range("B6:C37,H6:H37").Copy End Sub When using...
  8. O

    Index Match function using non-contiguous columns in table

    I'm looking to return values, numeric, and text from an lookup (index match) from non-contiguous columns in a table. Ex. if Age is greater than 60 return:Last Name | First Name | Age | Full Age (yrs, months, days) <tbody> Smith John A. 67 67 years,8 month(s), 21 Days </tbody> This data would...
  9. K

    Convert a set of non-contiguous values into an array for use in SUMPRODUCT

    Goal is to get a set of non-contiguous values from a row into an array so it can be combined (e.g. SUMPRODUCT) with other arrays. The values in the array should be selected from the current row based on table column name. I'd thought I could do something like: =SUMPRODUCT(G5:G7, INDEX(...
  10. T

    Pass Fail percentage when columns are non-contiguous

    I am working on a spreadsheet of mostly pass or fail that has comments spaced throughout it. The person who made the spreadsheet used this formula to achieve a pass or fail percentage: =COUNTIF(M18:AX18,"Pass")/COUNTA(M18:AX18) But it counts the columns that have comments as a fail so it...
  11. Z

    AVERAGEIF over non-contiguous range...there has to be a better way

    I'm trying to average three non-contiguous cells that contain referential formulas, based on the criteria that the values are >0 (I want to ignore 0 values in the average). Everything I can think of either doesn't work or will be stupidly complex. There must be a simple way to use criteria...
  12. A

    Non-Contiguous range to array to populate multicolumn ListBox... a nice and simple question! Hi All, I've been putting together a Userform, which is designed to cherry-pick a selection of records (rows) from a worksheet, and use the data contained in each cherry-picked record to populate a multicolumn ListBox. My key issues are: 1) The records...
  13. M

    Need a formula to average the most recent X # of data points, non-contiguous cells & w/ blanks

    Excel ver. 2010, 32-bit OS Windows 7 Pro, 32-bit, Service pack 1 Normally I can find what I'm looking for online or on the boards but this one has me stumped. My actual sheet has hundreds of rows and columns but here is a simple example illustrating my challenge: Below is a table of students...
  14. T

    Combining non-contiguous ranges

    Apologies in advance if my description is vague. I'm not even sure how to describe what I'm trying to do. I have a range on a worksheet that will be used for data entry. There are a potential of 200 rows in this range. I need to repeat what was entered in this range on another tab, but without...
  15. F

    Help - VBA Copy Non-Contiguos cells and Paste to specific cells in another workbook.

    Thanks to Ron De Bruin for this wonderful code. I copy/pasted it and have been hacking at it here and there to try and make it fit my situation. I have limited experience with VBA so please forgive my butchering of this. I would like to set the source cells (which are non-contiguos) and paste...
  16. G

    Copying Non-Contiguous Range with a variable

    Ok, So I was hoping to figure out the rest of this on my own, but I am stuck. Currently, the code copies the entire row to the associated worksheet. What I am trying to do now is adjust the range so columns B through D are not copied as they are blank. I have managed to adjust the range to...
  17. F

    Userform get value of row one in column of selected cell

    Hi all, I have a userform that retreives data from a selected spreadsheet that has date values in columns A,B,F,G,K,L,P,Q,U,V as non contiguous data. I have a find that selects the maxVal date cell, then I want it to go to the first row in the column to get the header data along with the...
  18. 0

    Delete Non-Contiguous Columns Issue

    After research I have come up with this code to dump my data to another file and save it, then clear the information and formatting that is not needed. However, the row identified below with '<===== errors out ever time with the error: Delete method of Range class failed Your help is greatly...
  19. W

    Look up & add multiple entries along non-contiguous columns!

    Hey y'all, I need to look up a set item across a number of non-contiguous columns, then add together the total of all entries listed. Any suggestions? There is an example below: Assumptions: First cell A1, answer in cloumn AD. So in the column AC I will add up all the instances of "gear...
  20. S

    Deleting non-contiguous columns based on column number

    Hey everyone, I'm having a little trouble. I was able to select one group that I wanted to delete based on the column number because I am using a variable: Range(Columns(10), Columns(testint - 10)).Select Selection.Delete Shift:=xlToLefttestint is just the number of columns in the sheet But I...

Watch MrExcel Video

This Week's Hot Topics

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back