Clear Defined names in a Spreadsheet and define new name to a range of cell

[deepaksethu]

Hi group,

I am new to the world of macros. I would like to know the procedure to clear all existing names defined(bookmarks) in a spreadsheet and assign new bookmarks to the selected row/range of cells against the value/name in the first row of the spreadsheet.

say I have the following as :

Bookmark Name A B C D E F Row 1 1 2 3 4 5 6 Row 2 7 8 9 10 11 12 Row 3 13 14 15 16 17 18 Row 4 19 20 21 22 23 24
Let's assume now that the row 1 with values 1 to 6 has has the bookmark name's A to F . Now I want a procedure to clear those values and set those bookmark values to the highlighted row, say Row 4.

So after the procedure is run, the bookmark will refer to the the cell that has the value 19, B to the cell that has the value 20 and so on.

Thanks

[CBrine]

deepaksethu,
I think this is what you are going for, but I'm not sure. It seems like you have a column of data named A for column A, and B for column B,etc...., and you want to change the named range so that it references the highest row(Largest row number, not value). If that's correct, give this a try, otherwise let me know what I'm interpreting wrong.

[vba]
Dim n As Name, wb As Workbook
Set wb = ActiveWorkbook
For Each n In wb.Names
n.RefersTo = Range(Mid(n.RefersTo, InStrRev(n.RefersTo, ":") + 1, 50))
Next

[/vba]

HTH
Cal

[deepaksethu]

Cal,

Let's assume I have 10 rows of data with columns A to L. Also let's assume that each cell in row 1 has a pre-determined name defined.

Rquirement:

When I highlight a particular row, let's say 5, i want value of the cell highligted, point to the name defined against the correspondng cell in the row 1.


Say row 1 has names, a,b,c,d,e,f,g,h,i,j,k,l and I am having values 1,2,3,4,5,6,7,8,9,10,11,12. If i run this producedure, and when I call for the bookmark(c), I should get 3.

If i move the highlight to a different row, say row 7 and let's assume it has the values, 10,11,12,13,14,15,16,17,18,19,20,21. After I run this procedure, and I call for the value c, it should return 12

Thanks Cal for all the help.

[CBrine]

So you want to the defined name ranges for a to l to change from row one to row 7? or just the single cell you have highlighted?

If you highlight row 7, and column C, you want C to point to row 7 instead of row 1? What about the rest of the named ranges, do they continue to point to row 1, or do they shift to row 7 as well?

Also, since there is no association between the defined named ranges, we will have to use some sequencial ID, just like your example a,b,c,etc...

Cal