Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Create named range
I have a worksheet with rows of data. Col. A contains a
number. I sort the entire list by Col. A then I want to create a named range for each change in Col. A. For example, if Cells' A1, A2, and A3 all contain 999, then I want to take all the Col. A cells with 999 and create a named range of "999". Then, if Cells' A4, A5, A6, and A7 all contain 888, then I want to take those 4 cells and create a named range of "888". Any suggestions on the most efficent way to perform this task? Thanks in advance! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Create named range
First, "999" and "888" are not valid names. You could prefix or
postfix them with a character as I've done he one way: Public Sub NameRanges() Dim cell As Range Dim firstCell As Range Set firstCell = Range("A1") For Each cell In Range(firstCell, _ Cells(Rows.Count, 1).End(xlUp)) With cell If .Offset(1, 0).Value < .Value Then Range(firstCell, .Cells).Name = _ Format(.Value, "_000") Set firstCell = .Offset(1, 0) End If End With Next cell End Sub In article , "Robert" wrote: I have a worksheet with rows of data. Col. A contains a number. I sort the entire list by Col. A then I want to create a named range for each change in Col. A. For example, if Cells' A1, A2, and A3 all contain 999, then I want to take all the Col. A cells with 999 and create a named range of "999". Then, if Cells' A4, A5, A6, and A7 all contain 888, then I want to take those 4 cells and create a named range of "888". Any suggestions on the most efficent way to perform this task? Thanks in advance! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Create named range
Names have to start with a letter and can not resemble a range reference.
Sub Tester1() Dim Start As Range Dim cell As Range Dim vVal As Variant Set Start = Range("A1") vVal = Start.Value For Each cell In Range(Cells(1, 1), _ Cells(1, 1).End(xlDown)(2)) If cell.Value < vVal Then Range(Start, cell.Offset(-1, 0)) _ .Name = "Name_" & vVal Set Start = cell vVal = Start.Value End If Next End Sub -- Regards, Tom Ogilvy Robert wrote in message ... I have a worksheet with rows of data. Col. A contains a number. I sort the entire list by Col. A then I want to create a named range for each change in Col. A. For example, if Cells' A1, A2, and A3 all contain 999, then I want to take all the Col. A cells with 999 and create a named range of "999". Then, if Cells' A4, A5, A6, and A7 all contain 888, then I want to take those 4 cells and create a named range of "888". Any suggestions on the most efficent way to perform this task? Thanks in advance! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to create a dynamic named range? | Excel Discussion (Misc queries) | |||
#value! error trying to create a simple dynamic named range | Excel Discussion (Misc queries) | |||
how do i create a named range excluding particular cells | Excel Discussion (Misc queries) | |||
inserting a named range into new cells based on a named cell | Excel Discussion (Misc queries) | |||
Using a formula to create named range reference | Excel Worksheet Functions |