Rows Contained In Non-Contiguous Named Range
Dec 1, 2012
I'm trying to write a 'ReImport' macro to copy data from one model into another. I'll call these models 'Master' and 'Split'. 'Master' contains about 50 departments' worth of financial data, and the 'Split' files contain a Division's (between 1 and 20 departments') worth of data. Structurally, the 'Split' files are just copies of the Master with the unneeded departments hidden. They are sent to various Divisions for completion, then the ReImport process takes data from the Split files and copies it back into the Master. Every Split File contains the full 50 departments from the Master, and it is only the 'Visible Row' distinction plus a 'ReImport key' that should determine what gets ReImported.
Each 'Split' range has a range name that controls what Departments are hidden or visible, but the range name is not contiguous. In other words, Division A might show Departments 1, 4-8 and 23. (Reordering the Depts is not an option.)
In a perfect world, I want to evaluate ONLY rows contained within the Division range name for the ReImport key. But the non-contiguous aspect of the named range is throwing me off. So in the above example, JUST Departments 1, 4-8 and 23.
My second-best solution would be to search through Departments 1-23, though that would be much slower...
View 5 Replies
ADVERTISEMENT
Jan 20, 2010
I'm trying to do a vlookup on a non contiguous named range.
I have a non contiguous named Range called "temp".
View 6 Replies
View Related
Jul 14, 2012
I have as the two logical operators for an IF function the following COUNTA calculation which checks how many blanks are in the range GrdMtx7 and subtracts this from the value in E5 and adds the suffix "Module(s)".
Code:
(E5-COUNTA(GrdMtx7)-1)&" Module(s)",(E5-COUNTA(GrdMtx7)&" Module(s)")))
GrdMtx7 is a non-contiguous range consisting of the following cell references on a second sheet:
Code:
='Mod Schedule'!$E$7,'Mod Schedule'!$H$7,'Mod Schedule'!$K$7,'Mod Schedule'!$N$7,'Mod Schedule'!$Q$7,'Mod Schedule'!$T$7,'Mod Schedule'!$W$7,'Mod Schedule'!$Z$7,'Mod Schedule'!$AC$7,'Mod Schedule'!$AC$7,'Mod Schedule'!$AF$7,'Mod Schedule'!$AI$7,'Mod Schedule'!$AL$7,'Mod Schedule'!$AO$7,'Mod Schedule'!$AR$7,'Mod Schedule'!$AU$7,'Mod Schedule'!$AX$7
I need for the formula of the IF function to copy down so that it refers to F5, G5, H5, I5...etc.
I also need for the non-contiguous named range GrdMtx7 to refer to the same non-contiguous range shifted down one row intact for the COUNTA function to operate on the next row down, such that COUNTA is checking for blanks in the range:
Code:
='Mod Schedule'!$E$8,'Mod Schedule'!$H$8,'Mod Schedule'!$K$8,'Mod Schedule'!$N$8,'Mod Schedule'!$Q$8,'Mod Schedule'!$T$8,'Mod Schedule'!$W$8,'Mod Schedule'!$Z$8,'Mod Schedule'!$AC$8,'Mod Schedule'!$AC$8,'Mod Schedule'!$AF$8,'Mod Schedule'!$AI$8,'Mod Schedule'!$AL$8,'Mod Schedule'!$AO$8,'Mod Schedule'!$AR$8,'Mod Schedule'!$AU$8,'Mod Schedule'!$AX$8
I can't get got to work at all using OFFSET and would rather not create dozens or hundreds of named ranges incrementing one at a time - is there a way to achieve this, preferably with a formula, but vba if absolutely necessary.
My best effort doesn't work:
Code:
(E6-COUNTA(OFFSET(GrdMtx7,1,))-1)&" Module(s)",(E6-COUNTA(OFFSET(GrdMtx7,1,)))&" Module(s)")
View 2 Replies
View Related
Feb 14, 2008
I have a split named range covering non-contiguous areas, ie:
Name: status_web
Range: =$F$14:$I$122,$F$700:$I$746,$F$798:$I$830,$F$905:$I$933
This appears to work fine when selecting the name however it returns a #VALUE! error when using it in a formula, eg: = COUNTIF(status_web, "*p*"). Is there any way to get this to work, or do I need a separate named range for each contiguous section? NOTE: Not sure whether this extra fact would through a spanner in the works, but I am referencing this named range from a separate workbook.
View 3 Replies
View Related
Nov 11, 2012
I am running Excel 2003 on a Win7 system.
Here is my situation:
Each of my data sets spans roughly 75 columns by 250 rows at present, but this could expand. The first 7 rows contain metadata. Columns 2-25 or so contain the raw data, from which everything to the right is calculated. The data sets have most columns in common, but not necessarily all.
In order to tease out the most meaningful information from my data, I frequently sort all or part of it based on varying criteria. When I find a useful sorting criterion, I create a new column with a header that describes the criterion and populate it with a formula that returns a 1 if the condition of interest is met for that row, or a 0 if it is not. For example, if I am doing this in column AA, I might enter
=--(AND($AX8>$AA$4,$Y8>0))
and copy it down to the end of the data. The resulting vector of 1s and 0s quickly re-identify data that meets that criterion even after subsequent resorting. It also makes locating data that meets multiple sorting criteria extremely simple. Essentially, I create a truth table.
Cell $AA$4 in the above example contains a "comparator" value I might wish to change at some point, which would change the subset of data the condition selects for.
Here's the first hard part:
For each data set, I need the ability to generate meaningful plots that includes separate series based on the criteria I have described. However, I also need to retain the ability to resort the data or change the comparator value without disrupting these plots. In other words, the plots must NOT change when the order of the data is changed, but MUST change to display the appropriate data when the comparator changes.
Here's the 2nd hard part:
Once I have this working for one data set, I need to be able to port it to other data sets (which are contained in other workbooks), so that I can compare equivalent plots from each. I also need to minimize the number of manual steps involved in doing so, to avoid human errors and excessive time consumption.
The only other possible complication I can think of at the moment is that, to this point, I have been inserting blank rows to isolate subsets that I do not wish to perform further sorting on from each other.
Right now I am angling toward VBA code that loops through the entire data set to generate base dynamic ranges using the column header row (row 1) as the names, and the entire column of data for the rangeloops through the truth table columns to generate "branch" row ranges for each of the sorting conditions,loops through the entire data set one more time to create "branch" ranges for each of the base ranges.
I could generate some code to accomplish a one-off solution for a given configuration of a single data set (provided there is not a list length limit in a chart series that I'd be violating)...but without a dynamic named range, I don't know how to get to something that would update appropriately. So in essence, I am still stuck at the dynamic range part of this.
View 1 Replies
View Related
Apr 13, 2008
I have a contiguous range of rows starting from row 5. ...It starts from row 5 but could end anywhere below.
The name of my Sheet is Tester.
Now in Tester, columns B,C, and D have numerical values in them. Using VBA, how can put in values in corresponding row of column A.
So if there are values from B1:D39 and B40 onwards is empty, then A1 will have value B1*C1*D1, then A2 will have B2*C2*D2 and so on until A39 = B39*C39*D39
**I need to write the code so the whole process is done fastest.The code may be long but the macro needs to run as fast as possible.
(Also, I remember using Screen.Updating = False when I wrote similar stuff many yrs ago. Would this be useful here?)
View 9 Replies
View Related
May 15, 2008
How do you select certain ranges in the same column to filter? The ranges I need are "c36:c50" & "c54:c68" & "c72:c87" & "c91:c155" & "c158:c172" & "c176:c202" all filtering for blanks. I can not filter from c36:c202 because there are blanks in the missing rows and they have to stay.
View 7 Replies
View Related
Oct 31, 2008
How can this named range be made: every odd row from AV13 to FA60 ? When I select all this with the mouse, name the range ("ScoresRange"), and then select the named range, the range only extends to row 45.
In the Define Names window, here is the start of the text:
View 14 Replies
View Related
Jun 17, 2009
I have a named range and I want to delete the rows which have zero value in the named range.
View 6 Replies
View Related
Dec 3, 2013
I have a Choose function that is used to determine which range I need. e.g. =Choose(Choice,Cell,Cell,...).
In each corresponding "Choice" cell is a typed range. e.g. $B$33:$L$70
I'm looking for a way to be able to use this typed range as the range for some chart data.
View 2 Replies
View Related
Nov 6, 2009
This is a floowup to the issue that was originally posted as "Returning MAX/MIN values from multiple rows in a named range ". I marked that post as solved since I have worked through part of the issue and since have a different one.
View 2 Replies
View Related
Jun 17, 2013
I have a folder with 8 files which have different names like John-s, Joe-K, Mary-j,....
There are 14 sheets in each file. One of the sheets in the files named Daily contain information in Range A5:G16 that I need to import from all 8 files into a separate file called Import.
View 4 Replies
View Related
Aug 16, 2014
Let's say you have a named range, Rng1, which consists of cells A1 & A2. In vba how would you report back what, if any, named range the following cells resides:
Code] .....
here are multiple named ranges so using intersect is not feasible. Essentially, through code, I will be given a range and I need to determine if that range if part of a named range.
View 5 Replies
View Related
Apr 1, 2014
I have a master sheet and 102 'advisor named sheets'. The master sheet is updated daily with information (number and text) along 1 row in different columns (A:W). I would like when the advisors name is typed (W) =joebloggs! for this whole row to be auto input on joebloggs sheet. There can be multiple of these entered daily for same person, so the information would need to populate on the next row so not to type over the previous entry.
View 3 Replies
View Related
Nov 15, 2009
I have a workbook that is used for storing test results that relate to people in different groups.
B4, C4, D4 are the surname, first name and group headings. This is what I want to filter by.
Column E is empty
F,G,H,I,J,K are the results.
Column L is empty
M is total of results
What I want to do is set an autofilter from B-L, only have drop downs on B,C,D. But in addition to this I want to have it that the range can dynamically change if another column of results is inserted.
To calculate my formulas in the worksheet I named/defined M4 as 'Total' so that I could then offset from this named cell to determine the range of test result columns, which works well. I just don't know how to do this in vba.
I have this to just get the autofilter running:
With ActiveSheet
.Unprotect ("password")
.AutoFilterMode = False
.Range("B4:L4").AutoFilter
.Protect ("password")
End With
I need the range to be B4: offset of the cell 'total' 0,-1
and I need to loop through from F - offset of the cell 'total' 0,-1 and turn the dropdown off on each of these headers.
View 9 Replies
View Related
Jun 2, 2006
I need to create a named range on multiple sheets with the same named range & i cant figure out how to do this. EG :- I want to create a named range called "_SubUnitRows" on sheet1 starting from "A1:A50" & other named range again called "_SubUnitRows" on Sheet2 starting from "A1:A25" ...
View 2 Replies
View Related
Jul 19, 2013
I'm trying to make my named ranges remember the values of the last active cells used within another named range. The purpose of this is to make my charts dynamically change dependant on two criteria selected. My spreadsheet currently updates itself as and when I change the active cell within a single named range, dynamically changing the chart data by using Lookup based on the active cells value. However I want to get away from having several charts showing, I would like to have a single chart which dynamically changes based on a second selection. So the first selection is for a department (Facility) which changes the chart data relevant to that department, the second selection is to dynamically change the chart shown for the pre selected department.
Picture2.jpg
Using the following code when updating just one criteria with several charts
VB:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Application.Intersect(ActiveCell, [MeasureType]) Is Nothing Then
[valMeasurePicked] = ActiveCell.Value [code].....
which works fine but I'm not sure how to add a second selection criteria because my code uses Activecell. I thought that the VBA needed to set the last used value of a range as a variable and therefore allow the second criteria to be selected but am not sure how to put it into practice.
View 2 Replies
View Related
Sep 9, 2006
I would like to be able to count the amount of entries in column C and depending on the amount group them in either groups of 3 or 4, all names would be unique...so if there are 14 names in the list they would need to be grouped in to two groups of 4 and two groups of 3, if there were 19 then 4 groups of 4 and 1 group of 3 etc to a maximum 50 people, the results could appearon a seperate worksheet say pasted on to the worksheet starting with the groups of 3 (so paste a group of 3 then skip 3 rows then paste groups of 4 skip 2 rows, the row skipping is to allow seperation and manual entry of extra data). There will never be groups of 5 or more and never less than 3
View 8 Replies
View Related
Nov 11, 2009
I am drawing a complete blank. If in a macro I have determined and built a string list of non-contiguous rows that need to be deleted (e.g. rows 3, 5, and 7) from a range of rows named "Prices", what would be the correct syntax?
For example, I know it isn't..
Prices("3:3", "5:5", "7:7").Rows.Delete
because this gives me a "Type Mismatch".
View 9 Replies
View Related
Feb 17, 2008
I have a problem with the following code.
I want to select multiple rows in a very huge spreadsheet. But, when I
use this code, I receive an error: Runtime error 1004, Method ' Range'
of Object '_global' failed. What is wrong? Or maybe there is other
better way to select automatically empty rows in a spreadsheet?
Public Sub delhol()
'
' delhol Macro
'
' Keyboard Shortcut: Ctrl+d
'
Dim k As Variant
Dim rr As String
k = Array(34, 35, 38, 39, 40, 77, 133, 182, 207, 209, 225, 226, 295,
299, 300, 338, 394, 437, 468, 470, 480, 481, 560, _
591, 599, 655, 712, 729, 746, 755, 756, 852, 860, 962, 990, 1005,
1006, 1077, 1081, 1082, 1083, 1114, 1176, _
1217, 1252, 1260, 1261, 1338, 1342, 1343, 1344, 1375, 1381, 1437,
View 4 Replies
View Related
Jan 8, 2006
Range:
C3,C14,C25,C34,C41
Criteria:
>0
sum-range:
C3,C14,C25,C34,C41
I can't get that to work since the commas in the range are throwing the function off.
View 14 Replies
View Related
Jan 8, 2006
range:
C3,C14,C25,C34,C41
criteria:
>0
sum-range:
C3,C14,C25,C34,C41
I can't get that to work since the commas in the range are throwing the
function off.
View 13 Replies
View Related
Aug 17, 2012
I know how to find the last row of a continuous range. it's simply:
Code:
someRng(someRng.Count).row
How do you find the last row of a non-contiguous range though? Assume the range is vertically laid out and I don't want to do any looping.
View 9 Replies
View Related
Apr 8, 2008
Is it possible to re-size a non-contiguous range? I'm guessing "no", and a Google trawl hasn't provided me with anything.
Here's what I'm trying to do:
dim mult_rng as range
dim rng2 as range
set mult_rng = range("A1:B1,D1:E1")
set rng2 = mult_rng.resize(rowsize:=10)
That is, my original range is comprised of non-contiguous cells in the same row. When re-sizing, I'm trying to increase the number of rows, but leave the columns the same.
Like I said, my gut instinct says that this isn't possible using the 'resize' property. Can anyone think of another method to use? My constraints are that the original mult_rng isn't always the same (and isn't always non-contiguous).
View 9 Replies
View Related
Oct 3, 2009
I want data to columns A, C and E, while I have important data in columns B and D. Should I make three queries? Should I make query and save the results to temporary place and then move all the results to those columns A, C and E?
Right now the copying happens like this:
If Not rsData.EOF Then
rngTarget.CopyFromRecordset rsData
Else
MsgBox "No records returned.", vbCritical
End If
So should I do
1) One query and move everything in the right places
2) Multiple queries
3) Something else
Does this depend a lot about how many results I expect? I'd say likely about 100, maybe less than 1500, never more than 10000
View 9 Replies
View Related
Mar 19, 2013
Need code that will search non-contiguous range for first empty cell, paste data into found cell and data into offset cells and end search. If not empty, move to next cell in non-contiguous range. If NO empties are found in entire range, a msgbox.
Non-contiguous range: Range("B2,B32,B62,B92,B122,B152,B182")
Pasted data: 1st range into found empty, 2nd range into range offset of empty.
View 9 Replies
View Related
Jan 25, 2009
When I insert a line to select the range on the Worksheet it does select the range named as temp. However, the array does fill but only with the first part of the range eg in this case A7:C44.
My aim is to create an array of size point1A( all the rows in the multiple ranges, 3 columns).
Example of a Range to be assigned to the Array point1A. temp = "A7:C44,D7:F44,G7:I44,K7:M44,N7:P44,Q7:S44,"
View 6 Replies
View Related
Jun 4, 2012
I am trying to create a large range composed of smaller ranges. Some of these ranges may overlap so I want to make sure when I add cells to the range they don't already exist. I know how to make sure the same value doesn't exist in a range, but not the same cell.
View 2 Replies
View Related
May 29, 2009
Is it possible, through VBA, to select the top right cell in a non-contiguous range? For example, I have defined A1, A3, and A5 as a range. How could I tell Excel to select A5 from within that range?
I am using Excel 2007.
View 9 Replies
View Related
Dec 17, 2006
I'd like to count non blank cells in 16 separate ranges (each range consists of 6 consecutive cells). The first range in the series is C9:H9. 3 cells are then skipped and the process is repeated with the next range, ie L9:Q9 then skip 3. The last range in the series of 16 is therefore EK9:EP9.
If the value of non blank cells = 2 in any of these 16 ranges then copy the contents of this range to C24:D24 in sheet " Record Form Games 3583". If C24:D24 is not empty copy these values to C25:C26.
If the value of non blank cells = 6 in any of these 16 ranges then copy the contents of this range to C22:H22 in sheet "Record Form Games 3583". If C22 is not empty copy these values to C23:H23.
The ranges colored yellow in the example workbook are the ones that need to be copied to the sheet Record Form Games 3583.
View 9 Replies
View Related
