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! |
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! |
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! |
All times are GMT +1. The time now is 05:41 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com