# Get the column-name/row-number from a named cell

#### Willem!

##### Board Regular
Hi!

Is there a way to find the colum-name and the row-number from a named cell?

I know that I can get the complete address using:

Code:
``Sheet1.Range("some_named_cell").Address``

Answer (e.g.): \$A\$1

I want to seperately get 'A' and '1' (in VBA).

Is this possible?

TIA,

Willem!

### Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
Hello Willem!,

The Range object has to properties just for that purpose: .Column and .Row
Rich (BB code):
``````  Dim C As Variant
Dim R As Long

With Sheets(1).Range("some_named_cell")
C = .Columns(.Column)
R = .Row
End With
``````

This code will return the column as a letter and the row as a number for a single cell or the first cell on a range.

Sincerely,
Leith Ross

Hello Willem!,

Ignore my previous post. This method is better. It works with Excel 2000 and up.
Code:
``````  Dim C As Variant
Dim R As Long
Dim RC As Variant

RC = Split(Range("A1").Address(True, False), "\$")
R = RC(1)
C = RC(0)``````
Sincerely,
Leith Ross

Hi Leith,

Thank you for your quick reply. When I use your code though, I get an odd small number (double) for the column-name instead of a letter.

When I change your code to:

Rich (BB code):
``````Dim C As Variant
Dim R As Long

With Sheets(1).Range("some_named_cell")
C = .Column
R = .Row
End With``````

I get a number for the column-name, which solves my problem also (I can use this number also).

So thank you very much!

Ah, well, even better then.

I will use the code in your second reply, works like a charm and I agree that it's better (without the with-statement).

Replies
3
Views
254
Replies
1
Views
163
Replies
12
Views
164
Replies
6
Views
135
Replies
3
Views
611

Threads
1,203,078
Messages
6,053,404
Members
444,662
Latest member
AaronPMH

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back