How To Use Named Ranges In VBA Code Using Columns And Variation On Range
Mar 17, 2014
I am trying to clean up my code by using named ranges so that it will still work if/when others add columns or rows to the spreadsheet. These should be pretty easy solutions for most of you but I can't seem to find any answers online.
Here are 2 examples of the code I am trying to update:
I was thinking that replacing "A" with "namedRange" would work but I suppose that's not how the Columns application works. How can I modify the code below to work with a named range instead of the fixed column "A"?
[Code].....
For the following I would have thought that replacing "AU" with "namedRange" would do the trick as that's how things have worked for me in the past using the Range application. Unfortunately I get an error when I make the change.
[Code] .....
View 2 Replies
ADVERTISEMENT
Nov 7, 2006
I am using the following Selection_Change Event to show a UserForm when a cell in 1 of 31 named ranges is selected.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
Dim i As Long
For i = 1 To 31
If Not Intersect(Target, Range("StatPost" & i)) Is Nothing And Target.Value = "" Then
If Target.Offset(0, -8).Value = "" Or Target.Offset(0, -7).Value = "" Or Target.Offset(0, -6).Value = "" Or Target.Offset(0, -5).Value = "" Or Target.Offset(0, -3).Value = "" Or Target.Offset(0, -2).Value = "" Or Target.Offset(0, -1).Value = "" Then....................
View 3 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
Mar 14, 2013
Merge two columns into one list in excel
I would like to combine List1 and List2 into a 3rd named range called List3. I was wondering if this were possible without using any additional cells/columns (i.e. I don't want to use Column C like in the example shown in the link above).
Here's the formula from the example:
Code:
=IFERROR(INDEX(List1,ROWS(C1:$C$1)),IFERROR(INDEX(List2,ROWS(C1:$C$1)-ROWS(List1)),""))
I've played around with it, but could not come with any that worked.
View 3 Replies
View Related
Oct 13, 2007
I am employing code to label dynamic ranges that takes the form of,
ActiveWorkbook.Names.Add Name:="dms", RefersToR1C1:="=OFFSET(DMS!R10C5,0,0, COUNTA(DMS!C5),COUNTA(DMS!R10))"
'dms_j
ActiveWorkbook.Names.Add Name:="dms_j", RefersToR1C1:="=OFFSET(DMS!R11C10,0,0,MATCH("" * "",DMS!C10,-1),1)"
'dms_p
ActiveWorkbook.Names.Add Name:="dms_p", RefersToR1C1:="=OFFSET(DMS!R11C16,0,0,MATCH("" * "",DMS!C16,-1),1)"
'dms_r
ActiveWorkbook.Names.Add Name:="dms_r", RefersToR1C1:="=OFFSET(DMS!R11C18,0,0,MATCH("" * "",DMS!C18,-1),1)"
'dms_t
ActiveWorkbook.Names.Add Name:="dms_t", RefersToR1C1:="=OFFSET(DMS!R11C20,0,0,MATCH("" * "",DMS!C20,-1),1)"
The dynamic ranges are getting entered but the problem is that the range "dms" overshoots by six cells into blank cells at the bottom of the table, and the rest of the ranges overshoot by ten cells into blank cell area. I have deleted, cleared and destroyed everything around the table and re-sized the active area to no avail.
View 9 Replies
View Related
Mar 22, 2012
I have a spreadsheet, but it came from another file using the detach sheet method. It has therefore taken with it, all the named ranges that are now superfluous.
It seems a bit long-winded to delete these named ranges one by one manually. Would there be an easy to understand script that would delete them all, so I can start with a "clean sheet".
I'm using Excel 2003.
View 5 Replies
View Related
Sep 9, 2006
I need to make named ranges from an unknown number of columns(at least 1) each with an unknown number of rows. Each column has the name of the named range as the first row, and then a variable number of rows containing part numbers.
I can do it 1 by 1, but id rather do it in a loop so that blanks dont cause errors. there will be different people using versions of this sheet with different model/part number information What i've tried: Count number of colums with row 1 containing data (11 max, which is more than will ever be used) add into array(I know i dont really need to add into the array, but i might use it later for some other code). The problem i'm having is finding the range of rows that need added to the named dynamic range and adding it.
modelcount = Range("G7") 'G7 (for now) contains =COUNTA(H1,I1,J1,etc)
For i = 1 To modelcount
Redim Preserve Models(0 To i)
Models(i) = Cells(1, i + 7)
Range1 = Cells(2, i + 7).Address(xlA1)
lastRow = Cells(rows.Count, i + 7).End(xlUp).Row
Range2 = Cells(lastRow, i + 7).Address(xlA1)
Reference = Cells(2, i + 7).Address(xlA1)
ThisWorkbook.Names.Add Name:=Models(i), _
RefersTo:="=OFFSET(Reference,0,0,counta(Range1:Range2),1)", Visible:=True
Next i
This gets me the range i need, but doesnt create the named range properly. If i go to insert>names>define, the named ranges are created, but they dont relate to the data in any columns. It shows the variable names rather than the cell range the variable represents.
View 2 Replies
View Related
Oct 13, 2013
Im trying to set a range using a named range and dont know the best way to do this. so far ive got
Set rDataRange = Range(Offset("datastart",1, 0) & ":" & Offset("datastart",LastRow, 0))
datastart is the named range in the sheet and im trying to make a new range that starts 1 row below where datastart is and end where lastrow is. not sure where i messed up on this but i get errors when i try to run this
View 3 Replies
View Related
Mar 13, 2012
I need to create hundreds of named ranges going down a single sheet.
The name of the first range is in cell a1 and is 13 columns wide and 7 columns high (a1:m7)
The next name is in a8 and the range is a8:m14 and so fourth
If it's easier on a separate sheet I can have a list of names I want in column A and then the cells they refer to in column B.
E.g.
A B
Range1 Sheet1!A1:M7
Range2 Sheet1!A8:M14
Range3 Sheet1!A15:M21
View 2 Replies
View Related
Apr 28, 2014
I have written this macro to convert into a csv file to run for all defined named ranges in the activesheet. It run jst perfect when I hit SAVE button and it creates that many different CSV files for each named range.
However I am trying to use same macro in the another file and the problem I am facing is there a lot more named ranges and I want to run the macro for only selected NAMED RANGE. In this case 2 Named Range / 24 Named range.
What part of code do I need to change and to what to make it work for just 2 named ranges ?
View 6 Replies
View Related
Mar 3, 2007
I have named ranges locally in the active file and a named range in a different file.
How do I call these ranges into the VBA code to evaluate them.
View 4 Replies
View Related
Sep 5, 2007
I am using Andy's scrolling chart, and it is working well. http://www.andypope.info/charts/Scrolling.htm. Is there a way to paste the data the chart is displaying into a new sheet? In VB I tried to copy the named range and paste it but it did not work.
View 2 Replies
View Related
Nov 30, 2007
I am using follwoing piece of code for offset function. For Some reason it is not working.
Reference = Cells(TableRowNumber, TableColumnNumber).Address
Col = Application.WorksheetFunction. CountA(rngF.Columns(TableRowNumber, TableColumnNumber).EntireColumn)
ActiveWorkbook.Names.Add Name:=TableName, RefersToR1C1:= _
"=OFFSET(Reference,0,0,Col-3,1)"
Col value is also being calculated to zero. TableRowNumber and TableColumnNumber are the variables which store the starting location of the column.
View 2 Replies
View Related
Jan 28, 2009
I have a countif formula on another tab collecting data from these cells. I want to define a combined name DecBestFL with cells A1:A14 and G1:G14. Can I do that in Excel 2007?
On the other tab I would use =countif(DecBestFL,G5) G5 = "Y"
View 9 Replies
View Related
Aug 14, 2014
I'm looking to use a do until/loop code to find a cell that equals a named range ("Clause") that is located on another worksheet, the code I have so far is:
[Code] ......
I've used something similar before and works, but I just cant get it to work. The values that I need to find the match to the ("Clause") cell are directly below the original activecell.
View 6 Replies
View Related
Jun 1, 2009
I am getting a compile error for named range, the code is below:
View 2 Replies
View Related
May 14, 2009
If I omit the column arguement in resize I get error.
Dim ii As Range
EndRow = MyPL.Range("c" & Rows.Count).End(xlUp).Row
Set ii = Range("Data")
q = (EndRow - ii.Rows.Count)
Names("Data").RefersTo = "=" & ii.Resize(ii.rows.count+q, ).address
End Sub
View 9 Replies
View Related
Nov 22, 2006
Anyone know code to count columns in a named range? Got some code that i want to utilize for multiple things so i cant hard code the column count in anymore.
Dim intColumnCount As Integer
Dim X As Integer
x=0
Range("Poo").select
intColumnCount = magic voodoo code
For Each cell In range("Poo")
x = X+1
If ((x / inColumncont) > 2) And ((x / intColumnCount) <= 3) Then 'if on actual row
If IsDate(Cell) Then ' if there is a date in actual
Cell.Interior.ColorIndex = 15 ' shade actual gray
Cell.Offset(-1, 0).Interior.ColorIndex = 15 ' shade prom gray
Cell.Offset(-2, 0).Interior.ColorIndex = 15 ' shade Sched gray
End If
End If
If x = (intColumnCount*3) Then 'went thru Sched, Prom, & Actual
x = 0 ' Reset x
End If
Next
left off a bunch more if statements that formats other stuff but u get the idea... Need Magic Voodoo code replaced with working code
View 3 Replies
View Related
Sep 17, 2007
I have a dynamically named range the contents of which I want to clear. However, I also want to clear the cell contents in the next column beside the range. So if my dynamically named range is A20:B40 can I clear the contents of A20:C40 without changing the initially named range. Background: The data I have is the result of an advanced filter. The range is named for printing purposes without the final column.
If I then change one of the raw data I want to run the filter again. To do this I need to clear the first filter.
Range("AIT_Change_Management").ClearContents
View 7 Replies
View Related
Apr 29, 2008
I am trying to hide columns within a named range, where the range corresponds with the values section of a pivot table.
My code hides an unexpected set of columns. It seems to be affecting a different range, depending on the number of columns in the pivot table, which is not part of the named range, as if there was some kind of offset.
Sub hideAll()
With ActiveSheet.Range("wholeyear")
.Range(.Columns(1), .Columns(12)).Hidden = True
End With
End Sub
View 5 Replies
View Related
Mar 11, 2008
This seems really basic, but I can't seem to find it...
Using Visual Basic, if I have a named range, lets say like "NAMEDRANGE", how can I refer to cells in that range by their position in the range? For example, if I want to refer to the cell in the 2nd row and 3rd column of the range.
Also is their an easy way to refer to the first(top-left) cell in a named range?
View 3 Replies
View Related
May 4, 2006
I have a spread sheet that I pull data from different columns on a particular row. The problem is the code I used works great as long as the column never moves from its current location. Is there a way to use a named range to make the following piece of code work, so no matter how many columns are added or deleted the data is pulled correctly?
View 2 Replies
View Related
Feb 10, 2010
I need to run a vlookup to find some data. But I have a lot of data about 600,000 lines. Currently this list is spread over several columns (as the limit is something like 50000). How can I make my reference range access multiple columns?
View 5 Replies
View Related
Jul 25, 2006
Range("B25").Name = "EndMull"
Its fine but if i insert a new row or column then it mucks the whole thing up. Is there away of naming them but if any cells, row or columns are inserted the range will automatically adjust to suit1
View 2 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
Sep 15, 2014
I am trying to make a UDF that searches for a header, grabs everything under the header, and pulls it somewhere else. My UDF has three parameters:
1) Output_Range: the named range where the parameters will be pulled to
2) Header: the header to search for in order to copy the data underneath it
3) WorkbookName: the name of the workbook to search in
It looks like this:
VB:
Function LoadParameters(Output_Range As Range, Header, WorkbookName As String)
MyTimer = Timer
'Defining the variables.
Dim HeaderCell, HeaderCellEnd, HeaderRange, Output_Range
[Code]....
View 3 Replies
View Related
Dec 30, 2008
I have some named ranges that refer to 5 pieces of data organized into a row. For example, the name MyNamedRange might refer to $C$5:$C$10.
I am trying to loop through each column and get the values in MyNamedRange, then change corresponding values in a different named range. However, when I try to use Offset to access the subsequent columns of MyNamedRange, it doesn't work. It only gets the value of the first column right, the rest return <EMPTY>.
Sample .......
View 10 Replies
View Related
Jul 17, 2006
How do I go about using named ranges instead of cell names(ie A4:A9)
Private Sub UserForm_Activate()
Dim lngRow As Long
Dim intIndex As Integer
UserForm1.ComboBox1.Clear 'Clear combobox
lngRow = 2 Do While Sheet1.Range("a" & lngRow).Value <> ""
For intIndex = 0 To UserForm1.ComboBox1.ListCount - 1
If UserForm1.ComboBox1.List(intIndex) = Sheet1.Range("a" & lngRow).Value
Then
Goto NextRow
End If
Next intIndex
UserForm1.ComboBox1.AddItem Sheet1.Range("a" & lngRow).Value
NextRow:
lngRow = lngRow + 1
Loop
End Sub
I wanted to change "a" to the defined range "search"?
View 7 Replies
View Related
Dec 5, 2007
the code to add two independent named ranges cell by cell in vba. Both of the named ranges have the same structure and the sum would be posted to a third area of the same structure cell wise.
View 7 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
