Auto Named Ranges (Excel 2000)

Topic

New Reply

Hi everybody

I was wondering if it is possible to create code that will name ranges automatically. I attach a workbook to show what I have in mind.
In example 1 there are blue cells and if you put your cursor on any of the blue cells you will see a range name.

My problem is however that I have several cases where I have to create more than 60 names that begin with the letters at the top of the column followed by the number on the left in the row. That mean the range name will consists of the letters at the top plus the number directly to the left of the particular cell.

The macro must run as long as there are numbers on the left.

Is this possible? Any ideas will be appreciated.

Regards

Reply | Quote

Replies

You cannot have several ranges with the same names, so you must delete the names in Example1 before trying to assign names in Example2. The following code will do that, you can modify it to suit your needs:

Sub MakeNames()
Const lngStartRow = 4 ‘ row 4
Const lngStartCol = 6 ‘ column F
Dim lngRow As Long
Dim lngMaxRow As Long
Dim lngCol As Long
Dim lngMaxCol As Long
lngMaxRow = Cells(65536, lngStartCol).End(xlUp).Row
lngMaxCol = Cells(lngStartRow, 256).End(xlToLeft).Column
For lngRow = lngStartRow + 1 To lngMaxRow
For lngCol = lngStartCol + 1 To lngMaxCol
Cells(lngRow, lngCol).Name = _
Cells(lngStartRow, lngCol) & Cells(lngRow, lngStartCol)
Next lngCol
Next lngRow
End Sub

Reply | Quote

Hans

Thank you very much. I copied the code into a module but the macro do not want to run. I attached the revised workbook. Can you please be so kind as to advice what I did wrong.

Regards

Reply | Quote

In your worksheet, the table begins in row 3, column 5 (column E), so the constants at the beginning of the macro must reflect that:

Const lngStartRow = 3
Const lngStartCol = 5

Reply | Quote

Hans

Thank you very much, works perfect.

This will definitely save me many hours of work.

Regards

Reply | Quote